自动生成insert数据的SQL脚本

自动生成insert数据的SQL脚本2010-03-06OK_008判断当表存在标识列的时候就要允许将显式值插入表的标识列中，设置：

Set Identity_insert TableName Off

判断数据类型：

如，varchar,char,datetime,nvarchar,nchar，uniqueidentifier时候，在insert字段中就要增加双引号，否则就不加。

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"--目标表
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 SqlInsertUnion 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