Sql Server - INSERT INTO SELECT to avoid duplicates

Posted by Ashish Gupta on Stack Overflow See other posts from Stack Overflow or by Ashish Gupta
Published on 2010-03-25T05:02:23Z Indexed on 2010/03/25 5:13 UTC
Read the original article Hit count: 402

Filed under:
|
|

I have following two tables:-

Table1
-------------
ID Name
1  A
2  B
3  C


Table2
--------
ID Name
1  Z

I need to insert data from Table1 to Table2 and I can use following sytax for the same:-

INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1

However, In my case duplicate Ids might exist in Table2 (In my case Its Just "1") and I dont want to copy that again as that would throw an error.

I can write something like this:-

IF NOT EXISTS(SELECT 1 FROM Table2 WHERE Id=1)
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 
ELSE
INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 WHERE Table1.Id<>1

Is there a better way to do this without using IF - ELSE? I want to avoid two INSERT INTO-SELECT statements based on some condition.

Any help is appreciated.

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql