Exporting data in a SQL SERVER table as a script

Tag:c   ext   a   int    use     data    

ALTER PROCEDURE [dbo]. [Usp_outputdata] @tablename sysname, @outputIdentitycolumn int as declare @column varchar DECLARE @columndata varch AR (8000) DECLARE @sql varchar (8000) DECLARE @xtype tinyint declare @name sysname declare @objectId int DECLARE @objectname sysname declare @ident int SET NOCOUNT ON set @objectId =object_id (@tablename) If @objectId is null--determine if an object exists begin Prin T ' the object not EXISTS ' return end set @objectname =rtrim (object_name (@objectId)) if @objectname is null or CHARINDEX (@ob Jectname, @tablename) =0--This judgment is not strict at the Begin print ' object not at current database ' return End If OBJECTPROPERTY (@objectId, ' Istab Le ') < > 1--Determine if the object is a table begin print ' The object is not a table ' return end--select @ident =status&0x80 from sys columns where [email protected] and status&0x80=0x80--if @ident is not null--print ' SET identity_insert ' [email  protected]+ ' on '--add whether to output the self-increment field column judging function (identitycolumn) Select C.name,c.xtype into #tempallcolums from syscolumns C where [EMail protected] ORDER BY C.colid--judge the self-increment column and decide whether to exclude it according to @outputidentitycolumn; 1 is inclusive, 0 is not contains if @outputIdentitycolumn = 0beginSelect so.name table_name,--table name Sc.name iden_column_name--Increment field name--ident_current (so.name) Curr_value--Self-increment field current value- -IDENT_INCR (So.name) Incr_value,--increment field growth value--ident_seed (so.name) Seed_value--Self-increment field seed value into #tempIDENTITY from sysobjects so Inner joins syscolumns scon so.id = sc.id and ColumnProperty (sc.id, Sc.name, ' isidentity ') = 1 Where Upper (so . name) = UPPER (@tablename) Delete from #tempallcolums the where name in (select Iden_column_name from #tempIDENTITY) drop table #tempIDENTITYend--end declare syscolumns_cursor cursor for select Name,xtype from #tempallcolums open Syscolumns_cursor s ET @column = ' Set @columndata = ' fetch NEXT from Syscolumns_cursor to @name, @xtype while @ @fetch_status < >-1 begi n if @ @fetch_status < >-2 begin if @xtype not in (189,34,35,99,98)--timestamp does not need to be processed, Image,text,ntext,sql_variant is temporarily not located Manager begin set @[email protected]+case when Len (@column) =0 then "Else", ' [email protected] set @[email protected]+case when Len (@columndata) =0 then ' Else ', ' ', ', ' End +case when @xtype in (167,175) and then ' "'" ' + ' [email protected]+ ' + ' "" "'--varchar,char when @xtype (231,239) then "N" "+" [email protected]+ ' + ' "" "" "--nvarchar,nchar when @xtype =61 and then" "" +convert ( char (at all), ' [email protected]+ ', 121] + ' "'--datetime when @xtype =58 then '" "" +convert (char), ' [email  protected]+ ', + + ' + ' "'--smalldatetime when @xtype =36 then '" ' +convert (char), ' [email  ' protected]+ ') + ' "'" '--uniqueidentifier else @name end END end Fetch NEXT from syscolumns_cursor into @name, @xtype end Close Syscolumns_cursor deallocate syscolumns_cursor Set @sql = ' Set NOCOUNT on select ' ' Insert ' [email protected]+ ' ( ' [email protected]+ '] values (' as '--', ' [email protected]+ ', ') ' from ' [email protected] print '-- ' [email protected] EXEC (@sql) If @ident is not NULL pRint ' SET identity_insert ' [email protected]+ ' OFF ' drop table #tempallcolums使用: Usp_outputdata ' contact ', 0 Result: Insert Contact (firstname,lastname,emailaddress,testdate) VALUES (n ' AAAAA ', n ' bbbbb ', n ' ccccc ', NULL) insert Contact (firstname,lastname,emailaddress,testdate) VALUES (n ' xxx ', n ' bbbbb ', n ' ccaaaa ', NULL) insert Contact (FirstName , lastname,emailaddress,testdate) VALUES (n ' AAAAA ', n ' bbbbb ', n ' ccccc ', NULL) Insert Contact (Firstname,lastname, Emailaddress,testdate) VALUES (n ' AAAAA ', n ' cc ', n ' ccccc ', NULL) Insert Contact (firstname,lastname,emailaddress, TestDate) VALUES (n ' aaaaaxxx ', n ' bbbbb ', n ' ccccc ', NULL) Insert Contact (firstname,lastname,emailaddress,testdate) VALUES (n ' ccccc ', n ' bbbbb ', n ' ccccc ', NULL)