CODEWITHSUNDEEP
sqlsql-server
Xufeng
Xufeng

Reputation: 6762

MSSQL - Insert a row into a table if the row does not already exist in the table

Hi I have this piece of code:

insert into table1(column1, column2)
select distinct id, somevalue from table1 where id in (select id from table2)

which basically is adding all the id's from table2 to table1 column1, while other columns in table1 share same values for all rows.

Now the problem is that I have to check whether an id from table2 already exists in table1 and only insert if it does not. How can I achieve that?

Upvotes: 0

Views: 153

Answers (3)

sunk
sunk

Reputation: 320

INSERT INTO TABLE1(COLUMN1, COLUMN2)
    SELECT distinct id, value 
    FROM Table2 
    WHERE id NOT IN (SELECT ID from table1)

Upvotes: 0

Karl Kieninger
Karl Kieninger

Reputation: 9129

You example insert is a little unclear. It looks like you are inserting the id into Table1.Column1 instead of into Table1.id. However, assuming that the sample is correct. There are a number of ways to do it.

Here's one that is just a little different and possible faster. Faster because of the use of GROUP BY instead of DISTINCT on this sort of simple data and the use of NOT EXISTS instead of a NOT IN or LEFT JOIN.

INSERT INTO Table1 (Column1, Column2)
SELECT id
      ,value
  FROM Table2
 WHERE NOT EXISTS (SELECT ID FROM Table1 WHERE Table1.Id = Table2.Id) 
 GROUP BY id
         ,value

Upvotes: 0

M.Ali
M.Ali

Reputation: 69494

I think you are trying to do something like this...

INSERT INTO table1(column1, column2)
SELECT DISTINCT t2.id, t2.somevalue 
FROM table2 t2 LEFT JOIN table1 t1
ON t2.id = t1.id
where t1.id IS NULL

Upvotes: 1

Related Questions