如何复制身份列中的数据?

2022-01-16 00:00:00 sql sql-server sql-server-2008-r2

我在服务器中有一个带有 identity 列的表，在另一台服务器中有另一个具有相同结构的表.现在我想将所有数据从一个表复制到另一个表，但我可以没办法……

I have a table with an identity column in a server and have a other table with same structure in another server.. Now I want to copy all data from one table to other table but I can't help it...

我已经创建了一个链接服务器..

I have already created a linked server..

我用这个:

insert into [server].[database].[dbo].[table1]
    select *
    from table2

我也使用这个没有标识列的查询代替 *

I also use this query without identity column in the place of *

insert into [server].[database].[dbo].[table1]
   select column1, column2
   from table2

我该怎么办?

推荐答案

如果要插入到第二个也有标识列的表中，则需要显式定义列列表您正在插入并省略 identity 列:

If you want to insert into a second table that also has an identity column, then you need to explicitly define the list of columns you're inserting into and omit the identity column:

insert into [server].[database].[dbo].[table1] (col1, col2)
   select column1, column2
   from table2

这样，SQL Server 可以在目标表中插入应有的标识值

This way, SQL Server can insert the identity values in the target table as it should

更新:

两种情况:

(1) 您想将旧表中标识列中的现有值插入到新表中 - 在这种情况下，您需要使用 SET IDENTITY_INSERT ON/OFF 在您的查询中:

(1) you want to insert the existing values from the identity column from the old table into the new one - in that case, you need to use SET IDENTITY_INSERT ON/OFF in your query:

SET IDENTITY_INSERT [192.168.1.6].[audit].[dbo].[tmpDTTransfer] ON 

INSERT INTO [192.168.1.6].[audit].[dbo].[tmpDTTransfer] (id, code, transfer1)
   SELECT  
       id, code, transfer1 
   FROM 
       tmpDTTransfer 

SET IDENTITY_INSERT [192.168.1.6].[audit].[dbo].[tmpDTTransfer] OFF

(2) 如果您不想插入现有的标识值，而只想插入其他列并让 SQL Server 在目标表中分配新的标识值，那么您不要'不需要在查询中使用 SET IDENTITY_INSERT ON/OFF:

(2) if you don't want to insert the existing identity values, but just the other columns and let SQL Server assign new identity values in the target table, then you don't need to use SET IDENTITY_INSERT ON/OFF in your query:

INSERT INTO [192.168.1.6].[audit].[dbo].[tmpDTTransfer] (code, transfer1)
   SELECT  
       code, transfer1 
   FROM 
       tmpDTTransfer

但在任何情况下，您都应该始终在目标表中明确定义要插入的列列表.

But in any you, you should always explicitly define the list of columns to insert into, in your target table.

请勿使用:

INSERT INTO [192.168.1.6].[audit].[dbo].[tmpDTTransfer] 
   .......

而是使用

INSERT INTO [192.168.1.6].[audit].[dbo].[tmpDTTransfer] (Code, Transfer1)
   .......

或

INSERT INTO [192.168.1.6].[audit].[dbo].[tmpDTTransfer] (Id, Code, Transfer1)
   .......

或任何你需要的.明确您要插入的内容！

or whatever you need. Be explicit about what you want to insert into!

相关文章