使用标识列将数据复制到链接的SQL Server表中 [英] Copy Data into Linked SQL Server Tables with Identity Columns

问题描述

我正在尝试使用Access表中的Access 2010将数据复制到相同的空链接SQL Server 2012表中：

I am trying to copy data Using Access 2010 from Access tables into identical, empty linked SQL Server 2012 tables using:

INSERT INTO SQLTable SELECT AccessTable。* FROM AccessTable ;

INSERT INTO SQLTable SELECT AccessTable.* FROM AccessTable;

在某些表上查询成功，但在其他表上查询失败，抱怨密钥冲突。失败的链接表都有Identity列。复制除标识列中的数据之外的数据按预期工作。

On some tables the query is successful but on others the query fails complaining of Key Violations. The linked tables that fail all have Identity columns. Copying the data except that in the identity columns works as expected.

不幸的是，我需要Identity列中的数据，并且不接受自动生成的值。

Unfortunately, I need the data in the Identity columns and the auto generated values are not acceptable.

如何传输数据，包括进入标识列的值？

How do I transfer the data including the values that go into the identity columns?

目前，链接的SQL Server表使用连接字符串进行链接  ODBC; Driver = { SQL Server  Native Client 11.0} ...如果我将连接字符串更改为ODBC; Driver = {SQL Server} ...那么一些（但不是全部）表被复制而没有
密钥违规错误!!

At present the linked SQL Server tables are linked using a connection string ODBC;Driver={SQL Server Native Client 11.0}... if I change the connection string to ODBC;Driver={SQL Server}... then some (but not all) of the tables are copied without Key Violation errors!!

有谁知道发生了什么以及我应该如何转移数据？

Does anyone know what is going on and how I should transfer the data?

谢谢

推荐答案

检查IDENTITY_INSERT选项上的SQL Server BOL（图书在线） 。

Check SQL Server B-O-L (Books-On-Line) on the IDENTITY_INSERT options.

 

这篇关于使用标识列将数据复制到链接的SQL Server表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！