自动生成Insert数据的SQL脚本

判断当表存在标识列的时候就要允许将显式值插入表的标识列中，设置：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 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