CODEWITHSUNDEEP
sqlsql-server
Sai
Sai

Reputation: 712

Merging tables with same name and schema in different databases

I have this table A in UAT with the following schema

A-UAT

Column 1,
Column 2,
Column 3

I have the same table in prod with the same schema

A-Prod

Column 1,
Column 2,
Column 3

New rows were added to UAT recently and at that time UAT did not have all the records of the Prod table. Now, I want to merge the newly added rows in UAT with prod. That is, whatever was newly added in UAT alone should go into prod without disturbing whatever was existing there already. Is there a way to do this in MS SQL server 2005/08?

Upvotes: 1

Views: 1175

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Assuming you are only interested in new rows, not updated rows, and that your table has a primary key:

INSERT dbo.[A-Prod](all cols except IDENTITY column) 
SELECT all cols except IDENTITY column FROM dbo.[A-UAT] AS x
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.[A-Prod] 
    WHERE key = x.key
  );

You can also limit the SELECT by date/time if such a column exists.

If the table has an IDENTITY column, and you need to maintain those values, and assuming that no new rows have been inserted in production, you can say:

SET IDENTITY_INSERT dbo.[A-Prod] ON;

INSERT dbo.[A-Prod](all cols)
SELECT all cols FROM dbo.[A-UAT] AS x
  WHERE NOT EXISTS 
  (
    SELECT 1 FROM dbo.[A-Prod] 
    WHERE identity_column = x.identity_column
  );

SET IDENTITY_INSERT dbo.[A-Prod] OFF;

If there have been any new rows inserted in production, then it is likely you will need to perform some manual work to resolve any conflicts.

Upvotes: 1

Related Questions