自动生成insert数据的SQL脚本
2008-09-03 10:00:04 来源:WEB开发网判断当表存在标识列的时候就要允许将显式值插入表的标识列中,设置:
Set Identity_insert TableName Off
判断数据类型:
如,varchar,char,datetime,nvarchar,nchar,uniqueidentifier时候,在insert字段中就要增加双引号,否则就不加。
UseTest
Go
Declare
@Table1nvarchar(128),
@Table2nvarchar(128),
@Sql1nvarchar(4000),
@Sql2nvarchar(4000),
@SqlIdentityinsertnvarchar(512)
Set@Table1=’DutyHistory’--源表
Set@Table2=’Duty’--目标表
IFObject_id(@Table1,’U’)IsNull
Return
IfIsnull(@Table2,’’)=’’
Set@Table2=@Table1
Set@SqlIdentityinsert=’’
Ifexists(select1Fromsys.columnswhereObject_id=Object_id(@Table1,’U’))
Set@SqlIdentityinsert=’select’’SetIdentity_insert’+@Table2+’On’’’
select
@Sql1=Isnull(@Sql1+’,’,’insertInto’+@Table2+’(’)+Quotename(name),
@Sql2=Isnull(@Sql2+’+’’,’’+’,’select@Sql1+’’select’’’)+’+CaseWhen’+Quotename(name)+’IsnullThen’’null’’Else’+
CaseWhenuser_type_idIn(175,61,239,231,58,98,36,167,241)Then’’’’’’’’’+Rtrim(’+Quotename(name)+’)+’’’’’’’’’Else’Rtrim(’+Quotename(name)+’)’End+’End’
Fromsys.columns
whereObject_id=Object_id(@Table1,’U’)
Set@Sql1=@Sql1+’)’
Set@Sql2=’selectConvert(nvarchar(max),’’IfObject_id(’’’’’+@Table2+’’’’’,’’’’U’’’’)IsNullReturn;’’)AsSqlinsertunionAll’+
@SqlIdentityinsert+’unionAll’+
@Sql2+’From’+Quotename(@Table1)+Case@SqlIdentityinsertWhen’’Then’’Else’unionAll’End+
Replace(@SqlIdentityinsert,’On’,’Off’)
execsp_executesql@Sql2,N’@Sql1nvarchar(4000)’,@Sql1
Result:
Sqlinsert
---------------------------------------------------------
IfObject_id(’Duty’,’U’)IsNullReturn;
SetIdentity_insertDutyOn
insertIntoDuty([id],[Name],[WorkDate],[WorkHours])select1,’Robert’,’011200812:00AM’,3.4
insertIntoDuty([id],[Name],[WorkDate],[WorkHours])select2,’Robert’,’012200812:00AM’,3.4
insertIntoDuty([id],[Name],[WorkDate],[WorkHours])select3,’Robert’,’013200812:00AM’,3.4
......
insertIntoDuty([id],[Name],[WorkDate],[WorkHours])select58,’Robert’,’0227200812:00AM’,4.5
insertIntoDuty([id],[Name],[WorkDate],[WorkHours])select59,’Robert’,’0228200812:00AM’,4.5
insertIntoDuty([id],[Name],[WorkDate],[WorkHours])select60,’Robert’,’0229200812:00AM’,4.5
SetIdentity_insertDutyOff
更多精彩
赞助商链接