On Duplicate Key Giving Syntax Error

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

On Duplicate Key Giving Syntax Error

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-04-22 : 17:31:52

I'm trying to go from examples I've found, but I'm getting "Incorrect syntax near the keyword 'ON'. Incorrect syntax near '='"
TABLE_TGT has a primary key consisting of both CustID and Territory. I'm not using SQL 2008, so I can't use the MERGE command.
-----------------------------------------


INSERT INTO TABLE_TGT
           ([CustID]
           ,[Territory]
           ,[SalesPersonID]
           )
SELECT
           [CustID]
           ,[Territory]
           ,[SalesPersonID]
FROM TABLE_SRC
ON DUPLICATE KEY UPDATE TABLE_TGT.[SalesPersonID] = TABLE_SRC.[SalesPersonID]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-04-22 : 17:34:42

"on duplicate key" is MySQL syntax. We are on MS SQL Server here.

Too old to Rock'n'Roll too young to die.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-04-22 : 17:51:06

Ooops! I'm sorry, but I didn't realize the example I was looking at was MySQL. I'm using SQL 2005. So is there a pre-2008 alternative that would accomplish the same thing? I can do a separate UPDATE - the main thing I want to duplicate is only inserting rows for keys that don't already exist.

quote:
Originally posted by webfred

"on duplicate key" is MySQL syntax. We are on MS SQL Server here.

Too old to Rock'n'Roll too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-22 : 19:13:28

If you want to provide sample data and expected output we can show you how to write it in SQL server. Since you are using SQL 2005, then you might want to search for an UPSERT - But basically, you need to do an UPDATE and then an INSERT.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources