I'm trying to import some data from a MS-Access file to my SQL Server database. I keep getting primary key errors because some of the data overlaps. Therefore I tried using ON DUPLICATE KEY UPDATE as well as INSERT IGNORE. Both seem to be unknown to my SQL Server (running 2008 R2) as I get syntax errors. Do I need some add-on library or is INSERT IGNORE and ON DUPLICATE KEY not usable when inserting with a select query to .mdb? Here's the code snippet:
INSERT INTO XCManager.XC_DATA1 (STATION_ID, SENSORNAME, TIME_TAG, ORIG_VALUE, ED_VALUE, SOURCE)
SELECT STATION_ID, SENSORNAME, TIME_TAG, ORIG_VALUE, ED_VALUE, SOURCE
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source=H:\OPERATIONS & MAINTENANCE SECTION\Modeling & Gauging\PCBase2\PCBASE2 Files.mdb')...RUMN3
ON DUPLICATE KEY UPDATE STATION_ID=STATION_ID
Here's the parsing result:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ON'.
SQL Server does not support INSERT IGNORE or ON DUPLICATE. That syntax is specific to MySQL.
If you had looked up the INSERT statement in the SQL Server manual you would have seen that.
You need to use the MERGE statement in order to update or insert.
when inserting with a select query to .mdb
I don't understand that part. If you have SQL Server you are not "inserting into a .mdb".
Are you maybe running MS Access instead? In that case the MERGE will not work either as far as I know (you would need to check the manual for MS Access for an equivalent statement)
not sure if this got resolved, but one way to accomplish "insert ignore" in sql server is to check the "ignore duplicates" box when creating a unique index on a set of columns for a table. When you do this, SQLServer will not throw an exception, just a warning, so if you bulk insert with an index like this, then it will ignore the dupes for you.
The trouble with this is, if you have a TON of rows (10s of millions or more) having an index on the table as you bulk insert will be slower.
