CODEWITHSUNDEEP
sqlsql-server
Curtis White
Curtis White

Reputation: 6353

Insert Multiple Rows If Not Exists

I have two tables. Table A and Table B. Table A has some unique id's already existing. I want to insert a new custom row into Table A where those rows for my condition exist in Table B and not in Table A. How do I insert all rows from Table B that don't exist in Table A? I am looking for MSSQL solution.

I have tried the following two forms but neither seem to work:

Example A:

INSERT into TableA
(
    Id, 
    Selection1, 
    Selection2,
    Selection3        
)
SELECT 
(
    Id,  /* this field comes from TableB */
     0,
     0,
     1   
)
from TableB 
where NOT EXISTS ( SELECT * FROM TableA
                INNER JOIN TableB
                ON TableA.Id = TableB.ID)

Example B

IF NOT EXISTS ( SELECT * FROM TableA
        INNER JOIN TableB
        ON TableA.Id = TableB.Id)

    INSERT into TableA
    (
        Id, 
        Selection1, 
        Selection2,
        Selection3
    )
    SELECT 
    (
        Id,  /* this field comes from TableB */
         0,
         0,
         1   
    )
    from TableB

Upvotes: 1

Views: 5071

Answers (3)

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

You're looking for MERGE. The following pseudo code will get you there:

MERGE INTO [TableA] AS [target]
using (SELECT distinct [Id] /* multiple ids exist in table a */
              , [Selection1]
              , [Selection2]
              , [Selection3]
       FROM   [TableB]) AS [source]
ON [target].[ID] = [source].ID
WHEN NOT matched THEN
    INSERT ([Id]
            , [Selection1]
            , [Selection2]
            , [Selection3])
    VALUES ([Id]
            , [Selection1]
            , [Selection2]
            , [Selection3]);

Upvotes: 5

Ruslan Tolkachev
Ruslan Tolkachev

Reputation: 644

Using EXCEPT could be performance improvement, because MERGE needs data to be sorted.

INSERT into TableA
(
    Id, 
    Selection1, 
    Selection2,
    Selection3        
)
SELECT Id,0,0,1   
from TableB 
EXCEPT
SELECT Id,0,0,1 
FROM TableA INNER JOIN TableB
                ON TableA.Id = TableB.ID

only thing is number and types of columns have to match in both SELECTs

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You are very close with the first query. The problem is the JOIN in the subquery:

INSERT into TableA (Id, Selection1, Selection2, Selection3)
    SELECT Id, /* this field comes from TableB */, 0, 0, 1
    from TableB b
    where NOT EXISTS (SELECT 1
                      FROM TableA a
                      WHERE a.Id = b.ID
                     );

Notes:

  • Table aliases make the query easier to write and to read.
  • Columns called Selection1, etc. are suspicious. You should probably be putting three rows into a "selections" table of some sort.

Upvotes: 1

Related Questions