MS SQL server 生成 insert 数据的存储过程脚本

cdchenli

• CREATEPROCEDUREdbo.UspOutputData
  
• @tablenamesysname
  
• AS
  
• declare@columnvarchar(1000)
  
• declare@columndatavarchar(1000)
  
• declare@sqlvarchar(4000)
  
• declare@xtypetinyint
  
• declare@namesysname
  
• declare@objectIdint
  
• declare@objectnamesysname
  
• declare@identint
  
• 
  
• setnocounton
  
• set@objectId=object_id(@tablename)
  
• 
  
• if@objectIdisnull--判断对象是否存在
  
• begin
  
• print'Theobjectnotexists'
  
• return
  
• end
  
• set@objectname=rtrim(object_name(@objectId))
  
• 
  
• if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密
  
• begin
  
• print'objectnotincurrentdatabase'
  
• return
  
• end
  
• 
  
• ifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是table
  
• begin
  
• print'Theobjectisnottable'
  
• return
  
• end
  
• 
  
• select@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80
  
• 
  
• if@identisnotnull
  
• print'SETIDENTITY_INSERT'+@TableName+'ON'
  
• 
  
• declaresyscolumns_cursorcursor
  
• 
  
• forselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid
  
• 
  
• opensyscolumns_cursor
  
• set@column=''
  
• set@columndata=''
  
• fetchnextfromsyscolumns_cursorinto@name,@xtype
  
• 
  
• while@@fetch_status<>-1
  
• begin
  
• if@@fetch_status<>-2
  
• begin
  
• if@xtypenotin(189,34,35,99,98)--timestamp不需处理，image,text,ntext,sql_variant暂时不处理
  
• 
  
• begin
  
• set@column=@column+casewhenlen(@column)=0then''else','end+@name
  
• 
  
• set@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','
  
• end
  
• 
  
• +casewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,char
  
• when@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,nchar
  
• when@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime
  
• when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetime
  
• when@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifier
  
• else@nameend
  
• 
  
• end
  
• 
  
• end
  
• 
  
• fetchnextfromsyscolumns_cursorinto@name,@xtype
  
• 
  
• end
  
• 
  
• closesyscolumns_cursor
  
• deallocatesyscolumns_cursor
  
• 
  
• set@sql='setnocountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablename
  
• 
  
• print'--'+@sql
  
• exec(@sql)
  
• 
  
• if@identisnotnull
  
• print'SETIDENTITY_INSERT'+@TableName+'OFF'
  
• 
  
• GO
  

CREATEPROCEDUREdbo.UspOutputData @tablenamesysname AS declare@columnvarchar(1000) declare@columndatavarchar(1000) declare@sqlvarchar(4000) declare@xtypetinyint declare@namesysname declare@objectIdint declare@objectnamesysname declare@identint setnocounton set@objectId=object_id(@tablename) if@objectIdisnull--判断对象是否存在 begin print'Theobjectnotexists' return end set@objectname=rtrim(object_name(@objectId)) if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密 begin print'objectnotincurrentdatabase' return end ifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是table begin print'Theobjectisnottable' return end select@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80 if@identisnotnull print'SETIDENTITY_INSERT'+@TableName+'ON' declaresyscolumns_cursorcursor forselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid opensyscolumns_cursor set@column='' set@columndata='' fetchnextfromsyscolumns_cursorinto@name,@xtype while@@fetch_status<>-1 begin if@@fetch_status<>-2 begin if@xtypenotin(189,34,35,99,98)--timestamp不需处理，image,text,ntext,sql_variant暂时不处理 begin set@column=@column+casewhenlen(@column)=0then''else','end+@name set@columndata=@columndata+casewhenlen(@columndata)=0then''else','','',' end +casewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,char when@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,nchar when@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetime when@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifier else@nameend end end fetchnextfromsyscolumns_cursorinto@name,@xtype end closesyscolumns_cursor deallocatesyscolumns_cursor set@sql='setnocountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablename print'--'+@sql exec(@sql) if@identisnotnull print'SETIDENTITY_INSERT'+@TableName+'OFF' GO