资源描述：
《为sql数据库表数据生成insert脚本.doc》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、为sqlserver数据库表数据生成insert脚本使用SQLServer自带的“生成SQL脚本”工具，可以生成创建表、视图、存储过程等的SQL脚本。那么，能否将表中的数据也生成为SQL脚本，在查询分析器中执行这些脚本后自动将数据导入到SQLServer中呢？答案是肯定的，网上收集资料如下：CREATEPROCEDUREdbo.OutputData@tablenamesysnameASdeclare@columnvarchar(1000)declare@columndatavarchar(1000)decl
2、are@sqlvarchar(4000)declare@xtypetinyintdeclare@namesysnamedeclare@objectIdintdeclare@objectnamesysnamedeclare@identintsetnocountonset@objectId=object_id(@tablename)if@objectIdisnull--判断对象是否存在beginprint@tablename+'对象不存在'returnendset@objectname=rtrim(object
3、_name(@objectId))if@objectnameisnullorcharindex(@objectname,@tablename)=0beginprint@tablename+'对象不在当前数据库中'returnendifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是表beginprint@tablename+'对象不是表'returnendselect@ident=status&0x80fromsyscolumnswhereid=@objecti
4、dandstatus&0x80=0x80if@identisnotnullprint'SETIDENTITY_INSERT'+@TableName+'ON'--定义游标，循环取数据并生成Insert语句declaresyscolumns_cursorcursorforselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colid--打开游标opensyscolumns_cursorset@column=''set@columndata
5、=''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+@nameset@col
6、umndata=@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),
7、'+@name+',121)+'''''''''--datetimewhen@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetimewhen@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifierelse@nameendendendfetchnextfromsyscolumns_cursorinto@name
8、,@xtypeendclosesyscolumns_cursordeallocatesyscolumns_cursorset@sql='setnocountonselect''insert'+@tablename+'('+@column+')values(''as''--'','+@columndata+','')''from'+@tablenameprint'--'+@sqlexec(@sql)if@ident
