Inserting One Row with Where Not Exists Clause

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

General SQL Server Forums

New to SQL Server Programming

Inserting One Row with Where Not Exists Clause

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-03-04 : 15:35:55

I am inserting records from a proprietary XML source that runs my SQL statement for every row of the XML source. The reason I mention this is it disqualifies use of MERGE or populating the INSERT with another SELECT statement referencing another table (the examples I've seen all have the second SELECT statement). I'll seek to replicate what is going on with the following statement.

This part of the statement works - it inserts a row:

INSERT INTO AD_WORK
VALUES (
'TESTS',
'TESTP',
'TESTD', 
0, 
0,
0,
0,
0,
123,
0,
0)

However, if I add a WHERE NOT EXISTS clause it gives an error:
"Incorrect syntax near the keyword 'WHERE'"

[code]INSERT INTO AD_WORK
VALUES (
'TESTS',
'TESTP',
'TESTD',
0,
0,
0,
0,
0,
123,
0,
0)
WHERE NOT EXISTS (select * from AD_WORK where SOURCE = 'TESTS' AND PRODUCT = 'TESTP')

I only want to insert the row if it does not already exist in the AD_WORK table.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-03-04 : 15:51:24

I think I figured it out on my own. They key here was going with the flow and using a second SELECT statement, though without a FROM clause referencing another table. So far this seems to work:

INSERT INTO AD_WORK
SELECT 'TESTS', 'TESTP','TESTD', 0, 0, 0, 0, 0, 123,0,0

WHERE NOT EXISTS (select * from AD_WORK where SOURCE = 'TESTS' AND PRODUCT = 'TESTP')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-04 : 23:01:09

quote:
Originally posted by BobRoberts

I think I figured it out on my own. They key here was going with the flow and using a second SELECT statement, though without a FROM clause referencing another table. So far this seems to work:

INSERT INTO AD_WORK
SELECT 'TESTS', 'TESTP','TESTD', 0, 0, 0, 0, 0, 123,0,0

WHERE NOT EXISTS (select * from AD_WORK where SOURCE = 'TESTS' AND PRODUCT = 'TESTP')

yep this is the correct way to do it
if there's only single set of values you could even do this


IF NOT EXISTS(select 1 from AD_WORK where SOURCE = 'TESTS' AND PRODUCT = 'TESTP')
INSERT INTO AD_WORK
VALUES (
'TESTS',
'TESTP',
'TESTD', 
0, 
0,
0,
0,
0,
123,
0,
0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources