请任何人可以帮助IDENTITY表 [英] Please Can Anyone help IDENTITY table

问题描述

alter PROCEDURE SP_TRUNCATE_AND_RESTORE
	  @CustDetails dbo.TempCustDetails readonly --User Define Table
AS
BEGIN
truncate table dbo.TBL_CUSTDETAILS 
SET IDENTITY_INSERT dbo.TBL_CUSTDETAILS ON 

insert into dbo.TBL_CUSTDETAILS
	select * from @CustDetails 
	
	
	SET IDENTITY_INSERT dbo.TBL_CUSTDETAILS OFF 
	
END
GO

错误

Error

Msg 8101, Level 16, State 1, Procedure SP_TRUNCATE_AND_RESTORE, Line 13
An explicit value for the identity column in table 'dbo.TBL_CUSTDETAILS' can only be specified when a column list is used and IDENTITY_INSERT is ON.

推荐答案

尝试这种方式...... :)

try this way...:)

SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] ON
INSERT INTO [MyDB].[dbo].[Equipment] (COL1, COL2) SELECT (COL1, COL2) FROM [MyDBQA].[dbo].[Equipment]
SET IDENTITY_INSERT [MyDB].[dbo].[Equipment] OFF

试试这个

Try this

alter PROCEDURE SP_TRUNCATE_AND_RESTORE
	  @CustDetails dbo.TempCustDetails readonly --User Define Table
AS
BEGIN
truncate table dbo.TBL_CUSTDETAILS 
SET IDENTITY_INSERT dbo.TBL_CUSTDETAILS ON 
 
insert into dbo.TBL_CUSTDETAILS (identityCol1, col2, col3)
	select identityCol1, col2, col3 from @CustDetails 
	
	
	SET IDENTITY_INSERT dbo.TBL_CUSTDETAILS OFF 
	
END
GO

这篇关于请任何人可以帮助IDENTITY表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！