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