如何使用临时表将标识列值从一个表插入另一个表。 [英] How do I insert identity column value from one table to another table using temp table.

问题描述

我无法从临时表中插入第二个表中的一个表中将标识列值插入到另一个表中。第二个表有条件，很少有列不允许插入空值。

EX： -

Hi,
I am not able to insert identity column value from one table into another table on insertion in second table from the temp table. There is condition with second table that few columns are not allow to insert null values.
EX:-

INSERT  INTO table1( firstName,lastName ) SELECT  firstName,lastName FROM    #tempRuleMaster;   

INSERT  INTO table2( id, firstName, lastName )SELECT  id, firstName,lastName FROM    #tempRuleDetails

id列在table2中是table1&的标识列。它是一种批量插入的条件。



我尝试过：



我已经尝试在table1&中插入时使用输出它不起作用。

id column in table2 is identity column from table1 & its a bulk insert kind of condition.

What I have tried:

I already tried using output at the time of insert in table1 & its not working.

推荐答案

标识列值通常是自动生成的，因此不允许设置它。所以基本上你的查询2应该没有标识，例如

Identity column value is typically auto-generated so it's not allowed to be set. So basically your query 2 should be without identity, for example

INSERT  INTO table2 (firstName, lastName )
SELECT  firstName,lastName FROM    #tempRuleDetails

有关其他信息，请参阅 IDENTITY （属性）（Transact-SQL） [ ^ ]



在极少数情况下，您确实需要设置标识列的值，您可以使用设置IDENTITY_INSERT（Transact-SQL） [ ^ ]。但正如所说，这通常不是必需的。

For additional info, see IDENTITY (Property) (Transact-SQL)[^]

In the rare situations where you really need to set the value of the identity column you can use SET IDENTITY_INSERT (Transact-SQL)[^] . But as said this isn't typically required.

使用IDENTITY_INSERT

Make use of IDENTITY_INSERT

INSERT  INTO table1( firstName,lastName ) SELECT  firstName,lastName FROM    #tempRuleMaster;   
 
SET IDENTITY_INSERT table2 ON
INSERT  INTO table2( id, firstName, lastName )SELECT  id, firstName,lastName FROM    #tempRuleDetails
SET IDENTITY_INSERT table2 OFF

这篇关于如何使用临时表将标识列值从一个表插入另一个表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！