Need help on insert/update - SQL Server Forums

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 on insert/update

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-06-16 : 08:00:46

I've tables and data as follow,


declare @t1 table
(DDate datetime, RID varchar(30), Posi tinyint, TripN varchar(20), Busn varchar(10), mflg bit);
insert into @t1 values('20100802','_R100400000001',1,'trip1','wkm1925',0);
insert into @t1 values('20100802','_R100400000001',2,'trip2','wnr4474',0);
insert into @t1 values('20100803','_R100400000001',1,'trip1','wkm1925',0);
insert into @t1 values('20100803','_R100400000001',2,'trip2','wkm4474',0);
insert into @t1 values('20100804','_R100400000001',1,'trip1','wkm1925',0);
/*@t1(DDate, RID, Posi) is unique*/

declare @t2 table
(DDate datetime, RID varchar(30), Posi tinyint, TripN varchar(20), Busn varchar(10), mflg bit);
insert into @t2 values('20100802','_R100400000001',1,'trip1','ack6796',0);
insert into @t2 values('20100803','_R100400000001',1,'trip2','wkm1925',1);
insert into @t2 values('20100807','_R100400000001',1,'trip1','wkm4474',0);
/*@t2(DDate, RID, Posi) is unique*/

1. I want to insert/update @t1 into @t2?
2. If row not exist in @t2, perform insert
3. If row exist in @t2 and mflg=0, perform update

My expected result as follow,

@t2
DDate     | RID             | Posi    | TripN        | Busn         | mflg
-----------------------------------------------------------------------------
20100802    _R100400000001    1         trip1          wkm1925        0 --- update
20100802    _R100400000001    2         trip2          wnr4474        0 --- insert
20100803    _R100400000001    1         trip2          wkm1925        1 --- not updated because of mflg=1
20100803    _R100400000001    2         trip2          wkm4474        0 --- insert
20100804    _R100400000001    1         trip1          wkm1925        0 --- insert
20100807    _R100400000001    1         trip1          wkm4474        0 --- not effected because existing row

How my T-SQL look's like?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-16 : 08:10:24

update t2
set Busn=t1.Busn
from @t2 as t2 inner join @t1 as t1
on t1.DDate=t2.DDate and t1.rid=t2.rid and t1.posi=t2.posi and t1.TripN=t2.tripN
where t2.mflg=0

insert into @t2
select * from @t1 as t1
where not exists(select * from @t2 as t2 where t1.DDate=t2.DDate and t1.rid=t2.rid and t1.posi=t2.posi and t1.TripN=t2.tripN)

select * from @t2

Madhivanan

Failing to plan is Planning to fail