欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-02-16 19:43:04
...
  1. CREATEPROCEDUREdbo.UspOutputData
  2. @tablenamesysname
  3. AS
  4. declare@columnvarchar(1000)
  5. declare@columndatavarchar(1000)
  6. declare@sqlvarchar(4000)
  7. declare@xtypetinyint
  8. declare@namesysname
  9. declare@objectIdint
  10. declare@objectnamesysname
  11. declare@identint
  12. setnocounton
  13. set@objectId=object_id(@tablename)
  14. if@objectIdisnull--判断对象是否存在
  15. begin
  16. print'Theobjectnotexists'
  17. return
  18. end
  19. set@objectname=rtrim(object_name(@objectId))
  20. if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密
  21. begin
  22. print'objectnotincurrentdatabase'
  23. return
  24. end
  25. ifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是table
  26. begin
  27. print'Theobjectisnottable'
  28. return
  29. end
  30. select@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80
  31. if@identisnotnull
  32. print'SETIDENTITY_INSERT'+@TableName+'ON'
  33. declaresyscolumns_cursorcursor
  34. forselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid
  35. opensyscolumns_cursor
  36. set@column=''
  37. set@columndata=''
  38. fetchnextfromsyscolumns_cursorinto@name,@xtype
  39. while@@fetch_status<>-1
  40. begin
  41. if@@fetch_status<>-2
  42. begin
  43. if@xtypenotin(189,34,35,99,98)--timestamp不需处理,image,text,ntext,sql_variant暂时不处理
  44. begin
  45. set@column=@column+casewhenlen(@column)=0then''else','end+@name
  46. set@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','
  47. end
  48. +casewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,char
  49. when@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,nchar
  50. when@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime
  51. when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetime
  52. when@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifier
  53. else@nameend
  54. end
  55. end
  56. fetchnextfromsyscolumns_cursorinto@name,@xtype
  57. end
  58. closesyscolumns_cursor
  59. deallocatesyscolumns_cursor
  60. set@sql='setnocountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablename
  61. print'--'+@sql
  62. exec(@sql)
  63. if@identisnotnull
  64. print'SETIDENTITY_INSERT'+@TableName+'OFF'
  65. 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