Insert data from one database to another database if record does not exist

Question

I've been searching for a couple of days and couldn't find a way to copy data from 1 database to another database.

I've referred to Copy tables from one database to another in SQL Server and How to copy row from a table to another table if the entry does not exist in the new table in sql - come out with some query and it showed [0 rows affected] (I've created some dummy records only in Source database to see if it is working)

Query as below:- [Column1 and Column2 are primary keys, Column1 can be the same but column2 has to be different]

USE Destination
INSERT INTO Destination.dbo.Table(Column1, Column2, Column3 etc...)
SELECT * FROM Source.dbo.Table(Column1, Column2, Column3 etc...) 
WHERE Source.dbo.Table.Column1= Column1 And Source.dbo.Table.Column2 Not In (Column2)

Appreciate if anyone can let me know what's wrong with the query / any similar question(s) I missed out to refer to / any method can work the same.

score 2 · Accepted Answer · answered Mar 18 '19 at 10:30

I think you can try to use exists and not exists to make it.

USE Destination
INSERT INTO Destination.dbo.Table(Column1, Column2, Column3 etc...)
SELECT t1.Column1, t1.Column2, t1.Column3 etc... 
FROM Source.dbo.Table t1
WHERE   
     exists (
        SELECT 1
        FROM Destination.dbo.Table t2
        WHERE t2.Column1  = t1.Column1
     )
And 
    not exists (
        SELECT 1
        FROM Destination.dbo.Table t2
        WHERE t2.Column2  = t1.Column2
     )

Cool, it is working now Thank you so much for your help. – TheButterfly Mar 18 '19 at 11:34 — TheButterfly, Mar 18 '19 at 11:34

Insert data from one database to another database if record does not exist

1 Answers1