7

I needed an SQL query that would update or create a database entry (if a certain customer doesn't exist yet). I found the current solution on the internet:

command.CommandText = "REPLACE INTO [Resource_Tracer].[dbo].[Customer](CustomerName, CustomerID) VALUES (@CustomerName, @CustomerID)"

Since I don't see it used a lot and actually never heard of it before, is this really the solution I want, or should I do this manually?

Bart Burg
  • 4,786
  • 7
  • 52
  • 87

2 Answers2

5

it is more common to use 

INSERT INTO table col1) VALUES (1)
ON DUPLICATE KEY UPDATE col1=VALUES(Col1)

replace into actually deletes a duplicate entry and inserts a new one.

juergen d
  • 201,996
  • 37
  • 293
  • 362
  • So, translated it should be: command.CommandText = "INSERT INTO [Resource_Tracer].[dbo].[Customer](CustomerName, CustomerID) VALUES (@CustomerName, @CustomerID) ON DUPLICATE KEY UPDATE [Resource_Tracer].[dbo].[Customer](CustomerName, CustomerID) VALUES(@CustomerName, @CustomerID)"? – Bart Burg Jul 11 '12 at 08:26
  • I did not notice you are using MSSQL. Then you should look into `MERGE`. See [this post](http://stackoverflow.com/questions/1197733/does-sql-server-offer-anything-like-mysqls-on-duplicate-key-update) – juergen d Jul 11 '12 at 08:35
5

Both REPLACE INTO and the ON DUPLICATE KEY alternative suggested are non standard SQL from the MySQL variant. So whether you use it depends on a) whether you're using MySQl and b) whether you want to be tied to that variant.

ANSI SQL defines a MERGE syntax that is more standard, if it is implemented on your platform

podiluska
  • 50,950
  • 7
  • 98
  • 104
  • This post: http://stackoverflow.com/a/10733485/1274398 suggests that it works from Sql Server 2008 and on wards. I cannot know if the user uses Sql Server 2005 or 2008, they are free to chose. Is there a 2005 equivalent? – Bart Burg Jul 11 '12 at 08:48
  • 1
    It was introduced into SQL Server in the 2008 version. There is no direct equivalent in SQL 2005. This may give you some pointers : http://sqlserver-tips.blogspot.co.uk/2006/09/mimicking-merge-statement-in-sql.html – podiluska Jul 11 '12 at 08:54