MS SQL server 生成 insert 数据的存储过程脚本 SQL ServerSQL脚本CC++
程序员文章站
2024-02-16 19:43:04
...
- 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 |