判斷當(dāng)表存在標(biāo)識(shí)列的時(shí)候就要允許將顯式值插入表的標(biāo)識(shí)列中，設(shè)置：Set Identity_Insert TableName Off
判斷數(shù)據(jù)類(lèi)型如，varchar,char,datetime,nvarchar,nchar，uniqueidentifier時(shí)候，在Insert字段中就要增加雙引號(hào)，否則就不加；

Use Test
Go
Declare
??????? @Table1 nvarchar(128),
??????? @Table2 nvarchar(128),
??????? @Sql1 nvarchar(4000),
??????? @Sql2 nvarchar(4000),
??????? @SqlIdentityInsert nvarchar(512)

Set @Table1='DutyHistory'??? --源表
Set @Table2='Duty'??????????? --目標(biāo)表

IF Object_id(@Table1,'U') Is Null
??? Return
If Isnull(@Table2,'')=''
??? Set @Table2=@Table1

Set @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.columns
Where 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

轉(zhuǎn)載于:https://www.cnblogs.com/wei9931/archive/2008/07/10/1240093.html

總結(jié)

以上是生活随笔為你收集整理的自动生成Insert数据的SQL脚本的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。