INSERT IGNORE和ON DUPLICATE KEY UPDATE在SQL Server 2008 R2中不起作用 [英] INSERT IGNORE and ON DUPLICATE KEY UPDATE not working in SQL Server 2008 R2

问题描述

我正在尝试将一些数据从MS-Access文件导入到我的SQL Server数据库。由于某些数据重叠，我不断收到主键错误。因此，我尝试使用 ON DUPLICATE KEY UPDATE 以及 INSERT IGNORE 。我的SQL Server（运行2008 R2）似乎都不知道我收到语法错误。我需要一些附加库，或者是在使用select查询插入时不可用的 INSERT IGNORE 和 ON DUPLICATE KEY 到.mdb？以下是代码段：

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

以下是解析结果：

消息156，15级，状态1，第3行

关键字'ON'附近的语法错误。

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'ON'.

推荐答案

SQL服务器不支持 INSERT IGNORE 或 ON DUPLICATE 。这个语法特定于MySQL。

SQL Server does not support INSERT IGNORE or ON DUPLICATE. That syntax is specific to MySQL.

如果您在 INSERT 您将看到http://msdn.microsoft.com/en-US/library/ms174335%28v=SQL.105%29.aspxrel =nofollow> SQL Server手册。

If you had looked up the INSERT statement in the SQL Server manual you would have seen that.

您需要使用 MERGE 语句以更新或插入。

You need to use the MERGE statement in order to update or insert.

当插入选择查询到.mdb

when inserting with a select query to .mdb

$ b $我不明白那部分。如果您有SQL Server，则不是插入.mdb。

您是否可以正在运行MS Access？在这种情况下， MERGE 将无法正常工作，只要我知道（您需要查看相应语句的MS Access手册）

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)

这篇关于INSERT IGNORE和ON DUPLICATE KEY UPDATE在SQL Server 2008 R2中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！