Help with ON DUPLICATE in MSQSL

[CXXXV]

The following statement does not run in SQLSERVER 2005

INSERT INTO Schedule (DATE, MEMBER_ID, QTY)
VALUES ('1/1/2009', 271, 99)
ON DUPLICATE KEY UPDATE QTY = 88

Any ideas why?

[jmcilhinney]

Because that is MySQL-specific syntax. If you want to know what SQL Server supports in the way of SQL key words then read the SQL Server documentation. If you want to write an "upsert" in SQL Server 2005 there is no such construct that specifically supports it. You simply write an IF statement with separate INSERT and UPDATE statements. SQL Server 2008 introduces the MERGE command to support upserts.

[CXXXV]

Actually I got this to work.

UPDATE Schedule SET QTY = 88
WHERE MEMBER_ID = 000 and DATE = '1/1/2009'
IF @@ROWCOUNT = 0
INSERT INTO Schedule (DATE, MEMBER_ID, QTY) VALUES ('1/1/2009',000,99)