WEB开发网
开发学院数据库MSSQL Server 自动生成insert数据的SQL脚本 阅读

自动生成insert数据的SQL脚本

 2008-09-03 10:00:04 来源:WEB开发网   
核心提示:判断当表存在标识列的时候就要允许将显式值插入表的标识列中,设置: Set Identity_insert TableName Off判断数据类型:如,自动生成insert数据的SQL脚本,varchar,char,datetime,nvarchar,nchar,uniqueidentifier时候,在insert字段中

判断当表存在标识列的时候就要允许将显式值插入表的标识列中,设置:

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

Tags:自动生成 insert 数据

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接