Share via

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

MR BILL 261 Reputation points
Jun 30, 2021, 3:09 PM

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,491 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,692 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 119.5K Reputation points MVP
    Jun 30, 2021, 3:34 PM

    You have forgotten the SELECT clause.


  2. 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;
    
    0 comments No comments

  3. 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:

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.