SQLTeam.com | Weblogs | Forums

Un exist data

sql2008

#1

I Have table employee :

ID Name
001 John
002 Belinda
003 Steven
004 Brandon

And I have another table contract contains employee :

ID Name
001 John
003 Belinda

I want to insert employee into contract for un exist employee only. What is the syntax?

thanks

Joe


#2

Your IDs and Names for "Belinda" contradict each other, but generally something like this:

INSERT INTO contract ( ID, Name )
SELECT e.ID, e.Name
FROM employee e
WHERE NOT EXISTS(SELECT 1 FROM contract c WHERE c.ID = e.ID)

#3

Thx a lot


#4

Another option is

INSERT INTO dbo.contract(ID, [Name])
SELECT e.ID, e.[Name]
FROM dbo.employee e
LEFT OUTER JOIN dbo.contract c ON e.ID=c.ID
WHERE c.ID IS NULL;

In many cases WHERE NOT EXISTS can be faster but not in this case as it still must check each row in the query result against the target table.