判断当表存在标识列的时候就要允许将显式值插入表的标识列中，设置：
Set Identity_insert TableName Off
判断数据类型：
如，varchar,char,datetime,nvarchar,nchar，uniqueidentifier时候，在insert字段中就要增加双引号，否则就不加。
Use TestGoDeclare @Table1 nvarchar(128), @Table2 nvarchar(128), @Sql1 nvarchar(4000), @Sql2 nvarchar(4000), @SqlIdentityInsert nvarchar(512)Set @Table1='DutyHistory' --源表Set @Table2='Duty' --目标表IF Object_id(@Table1,'U') Is Null ReturnIf Isnull(@Table2,'')='' Set @Table2=@Table1Set @SqlIdentityInsert=''If Exists(Select 1 From sys.columns Where Object_id=Object_id(@Table1,'U')) Set @SqlIdentityInsert='Select ''Set Identity_Insert '+@Table2+' On '''Select @Sql1=Isnull(@Sql1+',',' Insert Into '+@Table2+' (')+Quotename(name), @Sql2=Isnull(@Sql2+'+'',''+','Select @Sql1+'' Select ''')+'+Case When '+Quotename(name)+' Is null Then ''null'' Else '+ Case When user_type_id In(175,61,239,231,58,98,36,167,241) Then '''''''''+Rtrim('+Quotename(name)+')+''''''''' Else 'Rtrim('+Quotename(name)+')' End +' End'From sys.columnsWhere Object_id=Object_id(@Table1,'U')Set @Sql1=@Sql1+')'Set @Sql2= 'Select Convert(nvarchar(max),''If Object_id('''''+@Table2+''''',''''U'''') Is Null Return;'') As SqlInsert Union All '+ @SqlIdentityInsert+' Union All '+ @Sql2+' From '+Quotename(@Table1)+Case @SqlIdentityInsert When '' Then '' Else ' Union All ' End+ Replace(@SqlIdentityInsert,' On ',' Off')Exec sp_executesql @Sql2,N'@Sql1 nvarchar(4000)',@Sql1
Result:
SqlInsert
---------------------------------------------------------
If Object_id('Duty','U') Is Null Return;
Set Identity_Insert Duty On
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 1,'Robert','01 1 2008 12:00AM',3.4
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 2,'Robert','01 2 2008 12:00AM',3.4
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 3,'Robert','01 3 2008 12:00AM',3.4
... ...
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 58,'Robert','02 27 2008 12:00AM',4.5
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 59,'Robert','02 28 2008 12:00AM',4.5
Insert Into Duty ([id],[Name],[WorkDate],[WorkHours]) Select 60,'Robert','02 29 2008 12:00AM',4.5
Set Identity_Insert Duty Off
以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索sql
， insert
， select
， table
， nvarchar
， 显式值
IDENTITY_INSERT
自动生成sql 建表脚本、sql 生成insert语句、自动生成insert语句、sql server 生成脚本、java生成sql脚本，以便于您获取更多的相关知识。