带有标识列的导入和导出向导
我想将数据从一个数据库移动到另一个数据库，同时将标识列值保留在旧数据库中。当我运行向导时，出现验证错误“无法插入只读列“ChannelID”。
其中 ChannelID 是该表上的标识列。 如何覆盖身份功能以便能够导入数据？
I want to move data from one DB to another, while keeping the identity column values in the old DB. When I run the wizard I get a validation error "Failure inserting into the read-only column "ChannelID".
Where ChannelID is an identity column on that table.
How can I override the identity functionality in order to be able to import the data?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
您需要启用身份插入。单击“编辑映射”时会出现一个复选框。
You'll need to Enable identity insert. There's a check box when you click the Edit Mappings.
Byte56 的答案是正确的 - 从编辑映射屏幕中选中“启用身份插入”。我只是想指出“选择源表和视图”列表支持多选，因此您可以按住 Shift 或 Ctrl 来选择列表中的多个表。
Byte56's answer is correct - check "Enable identity insert" from the Edit Mappings screen. I just wanted to point out that the "Select Source Tables and Views" list supports multi-select, so you can hold down Shift or Ctrl to select multiple tables in the list.
在编辑列映射中，勾选启用身份插入。
然后单击编辑 SQL 按钮。
更改
为
导入 MS Access 数据库时 Works。这会保留自动编号字段中的数据转换为标识列。
In EDIT COLUMN MAPPINGS, Tick ENABLE IDENTITY INSERT.
Then click EDIT SQL button.
Change
into
Works in importing MS Access database. This preserves the data in Autonumber field being converted to Identity column.
您应该使用
SET IDENTITY INSERT ON来允许在IDENTITY列中插入值这是一个链接到 MSDN
You should use
SET IDENTITY INSERT ONto allow the insert of values in an IDENTITY columnHere is a link to MSDN
使用 SSMS 2008 R2 的 SQL Server 2008 R2。
简单地在“编辑映射”上启用身份不会保留identity=true。我必须手动编辑每个表的 sql 并在 sql 代码中包含 IDENTITY 属性。
示例：ADD MS_IDENT numeric(18,0) IDENTITY NOT NULL，
SQL Server 2008 R2 using SSMS 2008 R2.
Simply enabling identity on the "Edit Mappings" does not retain identity=true. I have to manually edit the sql for each table and include the IDENTITY property in the sql code.
Example: ADD MS_IDENT numeric(18,0) IDENTITY NOT NULL,
我刚刚升级到 SQL Server Management Studio 2017。我尝试选中“编辑映射”对话框中的“使用身份”复选框，但这不起作用。更新现有表时，向导不允许您编辑 SQL。相反，我能够将其作为一个包保存在我的硬盘上。然后我在记事本中编辑该文件，搜索“身份”。果然，该属性被设置为 true，根据文件本身的注释，这意味着标识列的值是从源复制的。将其更改为 false 会导致目标中自动生成值。希望这有帮助。
I just upgraded to SQL Server Management Studio 2017. I tried checking the "Use identity" checkbox in the Edit Mappings dialog but this did not work. When updating an existing table, the wizard doesn't let you edit the SQL. Instead, I was able to save it as a package on my hard drive. I then edited the file in Notepad, searching for "identity". Sure enough, the property was set to true, which according to the comments within the file itself, means the value for the identity column is copied from the source. Changing it to false results in the value being auto generated in the destination. Hope this helps.
我花了一些时间试图找到解决方案，
我有一个包含 300 多个表的访问数据库，并且使用 SSMS2017 导入工具，如果不手动编辑每个表的配置，我无法让它保留主键和自动编号。 .. 不好..
最后我使用了 bullzip 制作的工具（MS Access to MSSQL），它允许我从 access 转换为 mssql（他们也有一个用于访问 MySQL 的工具），
该工具可以为你完成这项工作, 枯萎至如果您有一个小表，则可以编写脚本，或者直接发送到服务器。
所有 ID 都完好无损，自动编号也在那里。
工作完成。
I have spent some time trying to find a solution for this
I have an access database with 300+ tables and using SSMS2017 import tool I could not get it to preserve the primary key and auto number, without manually editing the config for each table.... not good..
In the end I used a tool made by bullzip, (MS Access to MSSQL) which allows me to convert from access to mssql (they also have a tool for access to MySQL)
the tool does the job for you, wither to a script if you have a small table, or directly to the server..
All ID's are intact and autonumbers are also in there..
job done.