Overwrite, if duplicate entries found

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

Overwrite, if duplicate entries found

Author

Topic

n4nature
Starting Member

7 Posts

Posted - 2012-09-13 : 20:33:36

I am trying to come up with sql statement that will overwrite in case there are duplicate existing entries in the table.

Here's the SQL statement (as found from some basic searches on internet) for that:

INSERT INTO [TEST_TABLENAME] ([TEST_COLUMN1], [TEST_COLUMN2]) VALUES('00000', 'newValue') ON DUPLICATE KEY UPDATE ([TEST_COLUMN2])='newValue'

Compiling this statement, gives an error message: "Incorrect syntax near the keyword 'ON'."

Basically it's saying the word "DUPLICATE" is incorrect.

Just a part of that statement, works perfectly fine, as below:

INSERT INTO [TEST_TABLENAME] ([TEST_COLUMN1], [TEST_COLUMN2]) VALUES('00000', 'newValue')

Anybody know where the problem is?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-13 : 20:35:54

Are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

n4nature
Starting Member

7 Posts

Posted - 2012-09-13 : 20:36:50

That is correct, MS SQL Server 2012

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-13 : 20:39:03

for MS SQL Server use merge

ON DUPLICATE is for MySQL

KH
[spoiler]Time is always against us[/spoiler]

n4nature
Starting Member

7 Posts

Posted - 2012-09-13 : 21:12:43

Merge worked fine. Thank you for showing the correct way!

However since I am programmatically inserting values to SQL table, I will have to create a temp table with specific values and then merge it with the original table. Hope this is an elegant way - it works though. So thanks again!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-13 : 21:41:44

you don't have to use a temp table to do it. Please show us your query

KH
[spoiler]Time is always against us[/spoiler]

n4nature
Starting Member

7 Posts

Posted - 2012-09-14 : 13:29:59

I am not really sure about the syntax to merge values to the existing table and hence I inserted those values to a temp table and merged the temp table to to-be-updated-table.

Here's the query that I wrote:

IF OBJECT_ID('tempdb..#temp_testing') IS NOT NULL
DROP TABLE #temp_testing

create table #temp_testing
(
Col1 nvarchar(80) not null,
Col2 nvarchar(200) not null,
);

INSERT into #temp_testing (Col1 , Col2 ) Values('22222', 'testValue6')
INSERT into #temp_testing (Col1 , Col2 ) Values('11111', 'testValue2')

MERGE TestDB.dbo.TestColumn
USING #temp_testing
ON #temp_testing.Col1 = TestDB.dbo.TestColumn.Col1
WHEN MATCHED AND #temp_testing.Col2 != [TestDB].[dbo].[TestColumn].[Col2] THEN
UPDATE
SET [TestDB].[dbo].[TestColumn].[Col2] = #temp_testing.Col2
WHEN NOT MATCHED THEN
INSERT (Col1, Col2)
VALUES (#temp_testing.Col1, #temp_testing.Col2);

DROP TABLE #temp_testing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources