SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inserting One Row with Where Not Exists Clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 03/04/2013 :  15:35:55  Show Profile  Reply with Quote
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'"


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

USA
107 Posts

Posted - 03/04/2013 :  15:51:24  Show Profile  Reply with Quote
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')
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/04/2013 :  23:01:09  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000