Need help to update/insert into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help to update/insert into table

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-15 : 00:43:20

Consider as follow,
1. Below is my running no table


declare @SysRunno table
(Pref varchar(10), ryear char(4), rmoth char(2), runn int);
/*ryear and rmonth is unique*/
insert into @SysRunno values('TR','2010','05',1);
insert into @SysRunno values('TR','2010','06',11);

2. Now, i'm capable to built resultset as follow,

declare @tInfo table
(tid varchar(30), ddate datetime, rid varchar(30), posi tinyint);
insert into @tInfo values('TR100600000012','2010-06-30 00:00:00','_R100400000001','1');
insert into @tInfo values('TR100600000013','2010-06-30 00:00:00','_R100600000001','1');
insert into @tInfo values('TR100700000001','2010-07-01 00:00:00','_R100400000001','1');
insert into @tInfo values('TR100700000002','2010-07-01 00:00:00','_R100600000001','1');

/*
1. you'll see, my TID is generated based on @SysRunno
2. TID format is TR<year(DDate)><month(DDate)><8 digit>
*/

My question is,
1. How can i update/insert TID in resultset into @SysRunno?
2. So, another proses to built the TID will clean from duplication of TID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 02:24:21

Post your expected result

Madhivanan

Failing to plan is Planning to fail

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-15 : 02:40:54

quote:
Originally posted by madhivanan

Post your expected result

Madhivanan

Failing to plan is Planning to fail

Hello sir,

my exptected result should be as follow


@SysRunno
Pref    | ryear    | rmoth   | runn
----------------------------------------
TR        2010       05        1
TR        2010       06        13
TR        2010       07        2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 03:07:39

[code]
update t1
set runn=t1.runn+t2.runn from @SysRunno as t1
inner join
(
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmonth,count(*) as runn from @tInfo
group by left(tid,2),year(ddate),month(ddate)
) as t2
on t1.Pref=t2.tid and t1.ryear=t2.ryear and t1.rmonth=t2.rmonth

insert into @SysRunno(Pref,ryear,rmonth,runn)
select left(tid,2) as tid,year(ddate) as ryear,month(ddate) as rmonth,count(*) as runn from @tInfo as t1
where not exists(select * from @SysRunno where pref=left(tid,2) and ryear=year(ddate) and rmonth=month(ddate))
group by left(tid,2),year(ddate),month(ddate)

select * from @SysRunno[/code]

Madhivanan

Failing to plan is Planning to fail

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-15 : 03:14:33

tq sir. you're guidance is my inpiration

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-15 : 03:21:29