自动生成Insert数据的SQL脚本

最新推荐文章于 2021-05-28 14:03:13 发布

转载最新推荐文章于 2021-05-28 14:03:13 发布 · 159 阅读

本文介绍了一种在SQL Server中处理显式插入标识列的方法，并通过动态SQL生成来适应不同数据类型的插入操作。重点讲解了如何针对特定表进行身份标识插入的开启与关闭，确保数据正确导入。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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