You have forgotten the SELECT clause.
How to insert into db1.tableA from db2.tableA if not exists
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?
3 answers
Sort by: Most helpful
-
-
Guoxiong 8,206 Reputation points
Jun 30, 2021, 6:17 PM 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;
-
MelissaMa-MSFT 24,211 Reputation points
Jul 1, 2021, 2:04 AM 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.