CODEWITHSUNDEEP
sqlsql-server
TheButterfly
TheButterfly

Reputation: 85

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

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.

Upvotes: 1

Views: 1284

Answers (1)

D-Shih
D-Shih

Reputation: 46219

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
     )

Upvotes: 2

Related Questions