资源描述：
《sqlserver2005表中数据生成insert脚本》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、--sqlserver2005表中数据生成insert脚本--在查询分析器里操作：--第一步：新建存储UspOutputDataCREATEPROCEDUREUspOutputData@tablenamesysnameASdeclare@columnvarchar(1000)declare@columndatavarchar(1000)declare@sqlvarchar(4000)declare@xtypetinyintdeclare@namesysnamedeclare@objectIdintdeclare@objectnamesysnamedeclare@identintsetn
2、ocountonset@objectId=object_id(@tablename)if@objectIdisnull--判断对象是否存在beginprint'Theobjectnotexists'returnendset@objectname=rtrim(object_name(@objectId))if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密beginprint'objectnotincurrentdatabase'returnendifOBJECTPROPERTY(@objectId,'IsTab
3、le')<>1--判断对象是否是tablebeginprint'Theobjectisnottable'returnendselect@ident=status&0x80fromsyscolumnswhereid=@objectidandstatus&0x80=0x80if@identisnotnullprint'SETIDENTITY_INSERT'+@TableName+'ON'declaresyscolumns_cursorcursorforselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colidop
4、ensyscolumns_cursorset@column=''set@columndata=''fetchnextfromsyscolumns_cursorinto@name,@xtypewhile@@fetch_status<>-1beginif@@fetch_status<>-2beginif@xtypenotin(189,34,35,99,98)--timestamp不需处理，image,text,ntext,sql_variant暂时不处理beginset@column=@column+casewhenlen(@column)=0then''else','end+@names
5、et@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','end+casewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,charwhen@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,ncharwhen@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime
6、when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetimewhen@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifierelse@nameendendendfetchnextfromsyscolumns_cursorinto@name,@xtypeendclosesyscolumns_cursordeallocatesyscolumns_cursorset@sql='setnoc
7、ountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablenameprint'--'+@sqlexec(@sql)if@identisnotnullprint'SETIDENTITY_INSERT'+@TableName+'OFF'GO--第二步执行存储过程EXECKBC_OjbGridButton_PRO要
