旧数据库MSSQL身份字段问题！ [英] Old database MSSQL identity field issue!

问题描述

我将数据从旧数据库导入到新的MSSQL DB。



在我的数据库中我有一个字段IDcustomers这是一个int字段（不允许空值）。现在我希望IDcustomers字段也通过into语句为自动ID创建一个标识字段。



所以我用这个代码制作了一个nieuw专栏;

-

改变表[客户]

添加Id_new Int身份（1,1）

-

然后我想我将数据从IDcustomers复制到Id_newso我的数据属性继续

-

UPDATE dbo。[客户]

SET [customers]。[Id_new] = [customers]。[IDcustomers]

-



在上一份工作中，我只收到错误，因为我无法将数据复制到身份字段中。



如何修复它？

- 我可以在保持价值的同时为我当前的IDcustomers字段提供身份选项吗？或者是否有可能在Id_new字段中接管我的IDcustomers值？



我尝试过：



所以我用这个代码做了一个nieuw专栏;

-

改变表[客户]

添加Id_new Int标识（1,1）

-

然后我想我将数据从IDcustomers复制到Id_newso我的数据属性继续

-

UPDATE dbo。[客户]

SET [customers]。[Id_new] = [customers]。[IDcustomers]

-

解决方案

您不需要两列。只需要一个标识列IDcustomers。您可以在导入期间通过 SET IDENTITY_INSERT 启用插入标识列[ ^ ]属性。

  SET   IDENTITY_INSERT  [customers]  ON  
 
   -   导入数据 
 
  SET   IDENTITY_INSERT  [customers]  OFF  

I import data from an old database to a new MSSQL DB.

In my database I have a field "IDcustomers" this is a int field (not allow nulls). Now I want the "IDcustomers" field also make a identity field for automatic ID by a into statement.

So i used this code to make a nieuw column;
--
Alter Table [customers]
Add Id_new Int Identity(1, 1)
--
And then I thought I copy the data from IDcustomers to Id_newso my data estate continues
--
UPDATE dbo.[customers]
SET [customers].[Id_new]=[customers].[IDcustomers]
--

At the last job I only get an error because I can not copy my data to a identity field..

How do I fix it?
-Can I give my current IDcustomers field a identity option while maintaining the value? Or is there a possibility to take over my IDcustomers value in the Id_new field?

What I have tried:

So i used this code to make a nieuw column;
--
Alter Table [customers]
Add Id_new Int Identity(1, 1)
--
And then I thought I copy the data from IDcustomers to Id_newso my data estate continues
--
UPDATE dbo.[customers]
SET [customers].[Id_new]=[customers].[IDcustomers]
--

解决方案

You don't need two columns. Just have one identity column IDcustomers. You can enable inserting into the identity column during import via SET IDENTITY_INSERT[^] property.

SET IDENTITY_INSERT [customers] ON

-- import data

SET IDENTITY_INSERT [customers] OFF

这篇关于旧数据库MSSQL身份字段问题！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！