How to insert into db1.tableA from db2.tableA if not exists

Question

I'm trying to insert records from one database table to another database table where a record does not exist. So if db2.tbA record exists insert into db1.tblA. My current syntax looks something like:

INSERT INTO db1.dbo.tblA([Col1] ,[Col2] ,[Col3])
Where Not Exists (SELECT [Col1] ,[Col2] ,[Col3] FROM db2.dbo.tblA Where Col4 is null)

I'm getting a syntax error at the where not exists. What am I doing wrong?

Answer 1

You have forgotten the SELECT clause.

Answer 2

Try this:

INSERT INTO db1.dbo.tblA([Col1], [Col2], [Col3])
SELECT [Col1], [Col2], [Col3] FROM db2.dbo.tblA WHERE Col4 IS NULL
EXCEPT
SELECT [Col1], [Col2], [Col3] FROM db1.dbo.tblA;

Answer 3

Hi @MR BILL ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

You could refer below example and check whether it is helpful to you.

Suppose we have two tables like below:

We could use below statement:

INSERT INTO db1.dbo.tblA([Col1] ,[Col2] ,[Col3])  
SELECT [Col1] ,[Col2] ,[Col3] FROM db2.dbo.tblA Where Col4 is not null  

Then two rows inserted.

If we also need to exclude the existing data in db1.dbo.tblA, you could refer below:

INSERT INTO db1.dbo.tblA([Col1] ,[Col2] ,[Col3])  
SELECT [Col1] ,[Col2] ,[Col3] FROM db2.dbo.tblA Where Col4 is not null  
EXCEPT  
SELECT [Col1], [Col2], [Col3] FROM db1.dbo.tblA;  

Then only one row inserted.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.