Merge table from different sources

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 2008 Forums

Transact-SQL (2008)

Merge table from different sources

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-09-07 : 08:49:14

I have three tables tb1,tb2 and tbTotal. They have the same schemas.

What I want is to merge tb1 with tbTotal. I have done this and it works fines. My script:

;with cte as (select skey=@skey,col1=@col1,col2=@col2,col3=@col3)
merge tbTotal d
using cte s
on s.skey = d.skey
when matched then update
set col1=s.col1 ,
...
when not matched by target then insert
(skey, col1, ...)
values (s.skey, s.col1,...);

Also I want to remove all records in dt2 from dtTotal. I am not sure how to modify the code.

By the way, db1 and db2 can be empty.
Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 09:05:08

I am a bit confused by your example. You mentioned tb1, tb2 and tbTotal initially, but the query does not use those table names at all. Also, the cte is just one row - which seems unusual.

In any case, if you want to delete rows that are not in the source table, you would add another clause to the MERGE statement like this:

WHEN NOT MATCHED BY SOURCE THEN
	DELETE;

BUT PLEASE TEST IN A SAFE ENVIRONMENT BEFORE YOU DO IT. You don't want to end up deleting a lot of critical data!!!

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-09-07 : 10:18:04

I modified the code. The reason I use one row is that I loop the rows of tb1. How to combine the effect of tb2?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-09-07 : 10:26:25

I used stored procedure. If I merge tb1 directly, how can I pass it to the stored procedure? tb1 is created from my c# code. It is not a real table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-07 : 10:47:58

I am afraid I am not following the scenario that you described.

Hopefully someone else on the forum who can better interpret your description will respond.

You can also help by following the guidelines here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-07 : 15:57:29

You can certianly, do a merge to do the detete, although that's probably not as efficient as just doing a delete. But, are you looking for a new query or are you trying to combine the third table into your merge to do the insert and delete in one merge statement?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2012-09-08 : 07:41:26

Resloved it. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources