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
 On Duplicate Key Giving Syntax Error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 04/22/2013 :  17:31:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 04/22/2013 :  17:34:42  Show Profile  Visit webfred's Homepage  Reply with Quote
"on duplicate key" is MySQL syntax. We are on MS SQL Server here.


Too old to Rock'n'Roll too young to die.
Go to Top of Page

BobRoberts
Posting Yak Master

USA
107 Posts

Posted - 04/22/2013 :  17:51:06  Show Profile  Reply with Quote
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.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 04/22/2013 :  19:13:28  Show Profile  Reply with Quote
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
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.05 seconds. Powered By: Snitz Forums 2000