insert into and where exists

pts.
Tags:
SQL Server
Hi everyone, Can anyone tell me what is wrong with this query, I would be most grateful (the error I'm getting is Server: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'where'.) Here's the tsql Insert Into tbl_ScannedFiles (Folder, Path, FilName, DMIS, Loc, LName, SSN, PatCat, RegNbr) Values '21cshfolder', 'fullpath', 'FileName', '0109', 'IZ', 'TATE', '475527006', 'A99', '1234567') where NOT EXISTS (SELECT * FROM tbl_ScannedFiles WHERE FilName = 'FileName')

Answer Wiki

Thanks. We'll let you know when a new response is added.

You are missing an open parenthesis right after Values.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Fastfred1
    Try this (assuming FilName is the field containing your unique record identifier): Insert Into tbl_ScannedFiles (Folder, Path, FilName, DMIS, Loc, LName, SSN, PatCat, RegNbr) Values ('21cshfolder', 'fullpath', 'FileName', '0109', 'IZ', 'TATE', '475527006', 'A99', '1234567') where FilName NOT IN (SELECT FilName FROM tbl_ScannedFiles)
    0 pointsBadges:
    report
  • Bjack8468
    I apologize, that was a typo, I did include the open parenthesis, here is a corrected copy of my query: Insert Into tbl_ScannedFiles (Folder, Path, FilName, DMIS, Loc, LName, SSN, PatCat, RegNbr) Values ('21cshfolder', 'fullpath', 'FileName', '0109', 'IZ', 'TATE', '475527006', 'A99', '1234567') where NOT EXISTS (SELECT * FROM tbl_ScannedFiles WHERE FilName = 'FileName')
    0 pointsBadges:
    report
  • KingConan
    INSERT statements do not support WHERE clauses, you must use INSERT..SELECT to use a WHERE clause. INSERT tbl_ScannedFiles SELECT '21cshfolder', 'fullpath', 'FileName', '0109', 'IZ', 'TATE', '475527006', 'A99', '1234567' where NOT EXISTS (SELECT * FROM tbl_ScannedFiles WHERE FilName = 'FileName') I actually tested this for you by creating a simple table based upon the character types you were attempting to insert, so it works. Kevin
    0 pointsBadges:
    report
  • Bjack8468
    thanks for the quick response, you guys are awesome. I settled on using the suggestion made by KingConan Many, Many thanks. works great. woowhooo!!!
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following