Share via

SQL Server Equivalent of MySQL INSERT IGNORE

  • Article

Question

Tuesday, June 29, 2010 3:47 PM

I'm new to SQL Server and I'm trying to import data from an XML file into a database that I've created. The database has a number of tables, and some of these tables have foreign keys which reference primary keys in different tables in order to represent one-to-many relationships; however, the XML file is just parsed into a a series of dictionaries consisting of element/value pairs with redundant data.

When I am reading in the XML file and inserting the data into the relevant tables, it is expected that numerous dictionaries may contain the same foreign key value for some of the tables. Since the primary keys of the tables they reference are unique, trying to insert entries which already exist in the referenced table will cause a duplicate entry error.

MySQL has an elegant way of getting around this by having something like:

INSERT IGNORE INTO MyTable (Column1, Column2) VALUES ('Value1' , 'Value2')

This prevents duplicate entries being added to the table. However, this is not a valid command in SQL Server.

I've tried googling for a solution, however the only examples I could find were select entries from one table and inserting them into another, using a select statement and not in clause. What is the best way to insert given values into a table using SQL Server, such that duplicate values will be ignored?

All replies (8)

Thursday, July 1, 2010 4:17 PM âś…Answered | 1 vote

I found a solution on the internet which fixes it. You can use a command of the form:

INSERT INTO TableName (ColumnName) SELECT '" + value

+ "' WHERE NOT EXISTS ( SELECT ColumnName from TableName WHERE Name = '" + value + "')";

Followed by an ExecuteNonQuery().

Tuesday, June 29, 2010 4:35 PM

I believe that you could use MERGE to accomplisht this:

 

INSERT #temp
VALUES(1),
     (2),
     (4),
     (5);
GO

CREATE TABLE #new(
col1 INT
);
GO

INSERT #new
VALUES(3),
     (6),
     (2),
     (4);
GO

MERGE INTO #new n
 USING(SELECT col1 FROM #temp) AS t
 ON n.col1 = t.col1

WHEN NOT MATCHED 
  THEN INSERT VALUES (col1);
 GO
 
 SELECT *
 FROM #new;
 GO
 
 DROP TABLE #new;
 GO
 
 DROP TABLE #temp;
 GO

The result returned is:

3
6
2
4
1
5

 

Hope this helps

David Dye

Tuesday, June 29, 2010 4:54 PM | 2 votes

Another solution if you are using SQL 2005 or earlier you can use EXISTS()

IF NOT EXISTS (SELECT TOP 1 * FROM MyTable WHERE KeyCol = 'KeyValue')
BEGIN

 INSERT INTO <table>
 VALIES ('KeyValue', 'Value')
END

OR

INSERT INTO tbl1 (keycol, col1, col2)
SELECT keycol, col1, col2
FROM tbl2
WHERE NOT EXISTS
(SELECT 1
FROM tbl1
WHERE tbl1.keycol = tbl2.keycol);

 

Tuesday, June 29, 2010 6:15 PM

Thanks for the feedback guys.

From the use of flow control keywords in your examples, I'm guessing that you're using T-SQL, which I think might be beyon

I'm writing code in C# using an sql command object to execute an sql command against a particular data source, and was wanting to use a simple SQL statement to run in conjunction with the ExecuteNonQuery method (which is used to execute commands such as INSERT).

If I try to use control flow keywords in the command, such as "IF NOT EXISTS(SELECT...", it throws an exception because it can't parse the IF keyword. I was hoping to be able to get the results I wanted using a simple SQL statement which I could use with a sql connection object.

I posted in this forum because I thought the problem was more to do with writing the actual SQL Server query (rather than the sql command object). However if this post would be more appropritate for the C# forum please let me know and I will move it.

Thanks

 

 

Tuesday, December 20, 2011 7:55 AM

Thanks You Very Much for a great solution......

Thursday, October 17, 2013 1:39 PM

SELECT TOP 0 * INTO TempTable FROM MyTable;INSERT INTO TempTable (Column1, Column2) VALUES ('Value1', 'Value2');INSERT INTO MyTable SELECT A.* FROM TempTable A LEFT JOIN MyTable B ON A.UniqueField = B.UniqueField WHERE B.UniqueField IS NULL;DROP TABLE TempTable;

Thursday, October 17, 2013 2:18 PM | 1 vote

Do not propose your own posts as answers - especially when it is both duplicative of another post (from years ago) and less efficient.  Your < left join/ is null > is effectively the same as the <not exists> query - only less obvious and more confusing.

Tuesday, June 9, 2015 3:14 PM

If the data will new data then what should do? Your given solution is when I select from other table?