Merge tables - 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

General SQL Server Forums

New to SQL Server Programming

Merge tables

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2009-05-29 : 17:48:34

Hi,

I have table 1 which is located both in my production and test database. Due to a mistake, some users have been updating table 1 in production and others have been updating table 1 in test. How do I find out which records are different between the 2 tables and then merge them into the table in production?

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 22:56:09

if both tables having same noofcolumns with same datatype the use union

select col1,col2 from table1
union
select col1,col2 from table2 -- having same datatype

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 23:26:54

this will list the records in prod not in test and in test not in prod or any col that is diff


select *
from   [prod].dbo.table1 p
       full outer join [test].dbo.table1 t
       on p.pk = t.pk
where  p.pk is null      or t.pk is null
or     p.col1 <> t.col1  or (p.col1 is null and t.col1 is not null)
or     p.col2 <> t.col2  or (p.col2 is null and t.col2 is not null)

to insert records in test not in prod


insert into [prod].dbo.table1 (pk, col1, col2, col3 . . .)
select pk, col1, col2, col3 . . .
from   [test].dbo.table1 t
where  not exists
       (
            select *
            from   [prod].dbo.table1 p
            where  p.pk = t.pk
       )

for records existed in both DB but with col value difference, you have to decide to take prod or test,
to take prod, and ignore test, you don't have do anything. To take test and update prod db


update p
set    col1 = t.col1,
       col2 = t.col2
from   [prod].dbo.table1 p
       inner join [test].dbo.table1 t on p.pk = t.pk

and any where clause to it if necessary

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-30 : 02:32:36

quote:
Originally posted by nguyenl

Hi,

I have table 1 which is located both in my production and test database. Due to a mistake, some users have been updating table 1 in production and others have been updating table 1 in test. How do I find out which records are different between the 2 tables and then merge them into the table in production?

Thanks

if you're using sql 2008, you can use MERGE statement for this. it would be something like:-


MERGE prod.dbo.table1 as t1
USING test.dbo.table1 as t2
ON t2.pk=t1.pk
WHEN MATCHED THEN
     UPDATE SET col1=t2.col1,col2=t2.col2,..
WHEN NOT MATCHED BY TARGET THEN
     INSERT (pk,col1,col2,col3,..)
     VALUES (t2.pk,t2.col1,t2.col2,...)
WHEN NOT MATCHED BY SOURCE THEN 
     DELETE


if sql 2005 or less you've do this in three steps

INSERT INTO prod.dbo.table1
SELECT pk,col1,col2,... 
FROM test.dbo.table1 t1
LEFT JOIN prod.dbo.table1 t2
on t2.pk=t1.pk
WHERE t2.pk IS NULL

UPDATE t2
SET t2.col1=t1.col1,
t2.col2=t1.col2,..
FROM test.dbo.table1 t1
INNER JOIN prod.dbo.table1 t2
on t2.pk=t1.pk

DELETE t2
FROM prod.dbo.table1 t2
LEFT JOIN test.dbo.table1 t1
on t2.pk=t1.pk
WHERE t1.pk IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources