Insert Multiple Rows If Not Exists

Question

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

Curtis White · Accepted Answer · 2017-12-07 22:05:19Z

5

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]);

edited Dec 7, 2017 at 22:05

Curtis White

6,38312 gold badges62 silver badges85 bronze badges

answered Dec 7, 2017 at 21:45

Katherine Elizabeth Lightsey

8535 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-12-07 21:46:59Z

1

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.

answered Dec 7, 2017 at 21:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Ruslan Tolkachev · Accepted Answer · 2017-12-07 22:02:08Z

1

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

answered Dec 7, 2017 at 22:02

Ruslan Tolkachev

6444 silver badges9 bronze badges

Collectives™ on Stack Overflow

Insert Multiple Rows If Not Exists

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related