question

PaulBill-8748 avatar image
0 Votes"
PaulBill-8748 asked MelissaMa-msft commented

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?

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PaulBill-8748,

Could you please validate all the answers so far and provide any update?

If none is working, please provide your DDL, sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

You have forgotten the SELECT clause.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

Not really.

Do it like this: First write the SELECT statement that produces the rows you want. Once you have that running, slap on the INSERT part.

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @PaulBill-8748,

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:

110757-sample.png

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.


sample.png (8.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.