CODEWITHSUNDEEP
sqlsql-server
Legion
Legion

Reputation: 3427

How do I insert rows into one table from another that don't already exist in the target table?

I want to copy rows from Table2 into Table1 as long as they don't already exist in Table1. Right now I get the generic errors Incorrect syntax near the keyword 'WHERE'. and Incorrect syntax near the keyword 'NOT'.

 DECLARE @UserID varchar(100)
 SET @UserID = 'me'

 INSERT INTO Table1 (Col1, Col2, Col3, Col4, Col7,
                        Col8, Col9, Col5, Col6)
            (SELECT 2 AS [Col1], Col2, Col3, Col4, Col7,
                        Col8s, GETDATE() AS [Col9],  @UserID AS [Col5], GETDATE() AS [Col6]
            FROM Table2 AS bpt)
            WHERE (SELECT (CONVERT(varchar(10), Col2) + Col3 + Col4) FROM Table2) NOT EXISTS 
(SELECT (CONVERT(varchar(10), Col2) + Col3 + Col4) FROM Table1)

Upvotes: 0

Views: 53

Answers (4)

Laughing Vergil
Laughing Vergil

Reputation: 3756

Using your original query as the base, here are both the LEFT JOIN and IF NOT EXISTS versions of the query:

 DECLARE @UserID varchar(100)
 SET @UserID = 'me'

INSERT INTO Table1 (
    Col1, Col2, Col3, Col4, 
    Col7, Col8, Col9, Col5, 
    Col6)
SELECT 2 AS [Col1], 
    Col2, Col3, Col4, Col7,
    Col8s, GETDATE() AS [Col9], 
    @UserID AS [Col5], 
    GETDATE() AS [Col6]
FROM Table2 AS bpt
LEFT JOIN Table1 as exc
    ON bpt.Col2 = exc.Col2
    AND bpt.Col3 = exc.Col3
    AND bpt.Col4 = exc.Col4
WHERE bpt.Col2 IS NULL

-- Alternate version using EXISTS:
DECLARE @UserID varchar(100)
SET @UserID = 'me'

INSERT INTO Table1 (
    Col1, Col2, Col3, Col4, 
    Col7, Col8, Col9, Col5, 
    Col6)
SELECT 2 AS [Col1], 
    Col2, Col3, Col4, Col7,
    Col8s, GETDATE() AS [Col9], 
    @UserID AS [Col5], 
    GETDATE() AS [Col6]
FROM Table2 AS bpt
WHERE NOT EXISTS (
    SELECT *
    FROM Table1
    WHERE bpt.Col2 = exc.Col2
        AND bpt.Col3 = exc.Col3
        AND bpt.Col4 = exc.Col4
    )

I don't recommend the MERGE version for inserting if the row does not exist solely because MERGE is slower than either of these options.

Upvotes: 0

Daniel Gimenez
Daniel Gimenez

Reputation: 20494

Sometimes I prefer the organization of a MERGE statement over an INSERT, even though if all you do is insert with the MERGE then they are effectively the same thing.

In your case it allows you can compare each key as you would naturally do in a join, instead of concatenating strings to make a massive key.

MERGE table1 AS tgt
USING (
    SELECT 2 AS [Col1], Col2, Col3, Col4, Col7,
        Col8, GETDATE() AS [Col9],  @UserID AS [Col5], GETDATE() AS [Col6]
    FROM Table2
) AS src
ON src.Col2 = tgt2.Col2 AND src.Col3 = tgt.Col3 AND src.Col4 = tgt.Col4
WHEN NOT MATCHED THEN
    INSERT (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9)
    VALUES (src.Col1, src.Col2, src.Col3, src.Col4, src.Col5, src.Col6, src.Col7, src.Col8, src.Col9)

Here's an alternative method where you left join to the table and keep all that don't match. It'll work but I feel the logic on the merge is more straight forward.

INSERT INTO table1 (Col1, Col2, /* ... */ Col9)
SELECT src.Col1, src.Col2, /* ... */ Col9)
FROM (
   SELECT 2 AS [Col1], Col2, Col3, Col4, Col7,
       Col8, GETDATE() AS [Col9],  @UserID AS [Col5], GETDATE() AS [Col6]
   FROM Table2
) src
LEFT JOIN table1 tgt ON tgt.Col2 = src.Col2 AND tgt2.Col3 = src.Col3 AND tgt.Col4 = src.Col4
WHERE tgt.[PrimaryKey] IS NULL -- keep rows where there was no match to table1

Upvotes: 0

SQLApostle
SQLApostle

Reputation: 590

If you don't have primary keys to match on , try something like this 

INSERT Table1 (col1,col2)
SELECT col1,col2 from
(
     SELECT col1,col2 FROM table2
     EXCEPT
     SELECT col1, col2 FROM TABLE1
) A

Upvotes: 0

Sahil Bhalla
Sahil Bhalla

Reputation: 175

You can use below query to create the select statement for insertion in table 1.

SELECT col1,col2
FROM table2
WHERE NOT EXISTS (SELECT col1,col2 
                  FROM table1
                  WHERE table1.pk = table2.pk);

PK ----> Primary key

Upvotes: 2

Related Questions