| Author |
 Topic  |
|
|
n4nature
Starting Member
7 Posts |
 Posted - 09/13/2012 : 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)
Singapore
16766 Posts |
Posted - 09/13/2012 : 20:35:54
|
Are you using MS SQL Server ?
KH
Time is always against us
|
 |
|
|
n4nature
Starting Member
7 Posts |
Posted - 09/13/2012 : 20:36:50
|
| That is correct, MS SQL Server 2012 |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16766 Posts |
Posted - 09/13/2012 : 20:39:03
|
for MS SQL Server use merge
ON DUPLICATE is for MySQL
KH
Time is always against us
|
|
|
|
n4nature
Starting Member
7 Posts |
Posted - 09/13/2012 : 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)
Singapore
16766 Posts |
Posted - 09/13/2012 : 21:41:44
|
you don't have to use a temp table to do it. Please show us your query
KH
Time is always against us
|
|
|
|
n4nature
Starting Member
7 Posts |
Posted - 09/14/2012 : 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 |
|
|
| |
Topic |
|
Overwrite, if duplicate entries found
