If Exist - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If Exist

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2010-03-28 : 07:58:55

I want to insert records into mytable from temptable if records are not existed in mytable.

How can i write query to get it ? tks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-28 : 08:11:15

INSERT TargetTable (Col1)
SELECT DISTINCT t.Col1 FROM #Temp AS t
WHERE NOT EXISTS (SELECT * FROM TargetTable AS tt WHERE tt.Col1 = t.Col1)

N 56°04'39.26"
E 12°55'05.63"

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-28 : 09:00:11

Assuming that mytableID is the primary key for mytable and both tables have the same field names:


INSERT INTO mytable
SELECT t.*
FROM #Temp t
LEFT JOIN mytable m
	ON t.mytableID = m.mytableID
WHERE m.mytableID IS NULL

If mytableID is an identity, then you'll also need to specify a Column list in the insert clause:


INSERT INTO mytable (mytableID, Colx, Coly, Colz)

There are 10 types of people in the world, those that understand binary, and those that don't.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-28 : 11:45:53

If myTableID column is an identity column, you also need to set IDENTITY_INSERT on the table before using the column in an insert statement.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources