自动生成Insert数据的SQL脚本

最新推荐文章于 2019-08-03 07:53:27 发布

Andy-W

最新推荐文章于 2019-08-03 07:53:27 发布

阅读量1.6k

点赞数

分类专栏： MSSQL 文章标签： insert sql table object null 脚本

本文链接：https://blog.csdn.net/DVD_01/article/details/2524664

版权

MSSQL 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

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