导入数据时，启用身份插入不起作用 [英] Enable identity insert is not working when importing data

问题描述

我正在尝试使用导入向导将许多表从Access db导入到MS SQL Server.源表中的某些行已被删除，因此ID的顺序如下:2,3,5,8,9,12，...

I am trying to import many tables from access db to MS SQL server using the import wizard. Some rows in source tables has been deleted so the sequence of IDs are like this: 2,3,5,8,9,12,...

但是当我将数据导入到目标位置时，ID从1开始并以1递增，因此它们与源数据不完全匹配.

but when I import the data into my destination, the IDs start from 1 and increment by 1, so they don't exactly match with source data.

我什至还检查了启用身份插入"，但这无济于事.我发现的唯一解决方法是将目标表中的ID从身份更改为一个一个一个的整数，然后导入，然后再将其更改回身份，这非常耗时.

I even check the "Enable Identity insert" but it does not help. The only work around I have found is to change the IDs in destination tables from Identity to integer one by one, then import, and then change them back to identity, which is very time consuming.

还有更好的方法吗?

推荐答案

如果要在标识列中插入ID，则需要使用:

If you want to insert an id in the identity column, you need to use:

SET IDENTITY_INSERT table_name ON

https://msdn.microsoft.com/es-us/library/ms188059.aspx

请记住在脚本末尾将其设置为OFF.

Remember to set it OFF at the end of the script.

这篇关于导入数据时，启用身份插入不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！