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?
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 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 
INSERT INTO TABLE1(COLUMN1, COLUMN2) SELECT distinct id, value FROM Table2 WHERE id NOT IN (SELECT ID from table1) 
insert into . . . select id . . . from table1 . . ., all the ids are going to exist in table 1. Can you edit the question so it makes sense?MERGE: msdn.microsoft.com/en-us/library/bb510625.aspx