Question

我试图从MS-Access档案中将一些数据输入我的服务器库数据库。由于有些数据重叠,我不断发现主要的错误。因此,我尝试使用<代码>。页: 1 INSERT IGNORE。我的服务器(从2008年R2号机起)似乎都不知道这两件事,因为我有星号错误。是否需要增加图书馆或<代码>。 INSERT IGNORE和ON DUPLICATE KEY,在插入一个定点时不可使用? 这部法典:

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 SECTIONModeling & GaugingPCBase2PCBASE2 Files.mdb )...RUMN3 

ON DUPLICATE KEY UPDATE STATION_ID=STATION_ID

由此得出以下结果:

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

Answer 1

页: 1 服务器不支持<代码> INSERT IGNORE或ON DUPLICATE。这一辛迪加是我的SQL所特有的。

如果你在INSERT。页: 1

您需要使用 MERGE,以便更新或插入。

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)

Answer 2

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.

友情链接