Compare two database table rows and insert

I am working on a project where my requirement is just update the database from local server to the destination server (all tables, views, functions, rows and stored procedures).

Now I want to compare the local database table with the destination database table and insert the newly inserted rows from local database to the destination table.

开发者_Python百科

E.g. : I have a database dbsource and dbDestination and both contain a table table1. Now I insert new rows into dbsource.table1.

Now I want to compare both database tables and insert the new rows into the destination table.

Please help me .

---

Why reinvent the wheel?? There are lots of commercial applications out there that already do this for you:

• Red-Gate SQL Data Compare
• ApexSQL Data Diff

---

Assuming both Table1 tables have a Primary Key (unique) column here's how you can implement that. I name the PK column ID:

INSERT INTO DBDESTINATION.<SCHEMA_NAME>.TABLE1 
(SELECT T1.* FROM DBSOURCE.<SCHEMA_NAME>.TABLE1 AS T1
   LEFT OUTER JOIN DBDESTINATION.<SCHEMA_NAME>.TABLE1 AS T2 ON T1.ID=T2.ID
   WHERE T2.ID IS NULL)

Hope that helps.