SQL Server exports the insert into script

Create procedure DBO. uspoutputdata @ tablename sysname as declare @ column varchar (1000) Declare @ columndata varchar (1000) Declare @ SQL varchar (4000) 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 whether the object has a begin print 'the object not exist' return end set @ OBJ Ectname = object_name (@ objectid) If @ objectname is null or charindex (@ objectname, @ tablename) = 0 -- this judgment is not strict. Begin print 'object not in current database' return end if objectproperty (@ objectid, 'istable ') <> 1 -- determine whether the primary object is table begin print 'the object is not table' return end select @ ident = Status & 0x80 from syscolumns where id = @ objectid and status & 0x80 = 0x80 If @ ident is not null print 'set identity_insert '+ @ Tablename + 'on' declare syscolumns_cursor cursor for select C. name, C. xtype from syscolumns c Where C. id = @ objectid order by C. colid open syscolumns_cursor set @ column = ''set @ columndata ='' fetch next from syscolumns_cursor into @ name, @ xtype while @ fetch_status <>-1 begin 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 does not process begin set @ col Umn = @ column + case when Len (@ column) = 0 then 'else', 'end + @ name set @ columndata = @ columndata + case when Len (@ columndata) = 0 then ''else', '','', 'end + case when @ xtype in (167,175) then ''' + '+ @ name +' + ''' -- varchar, char when @ xtype in (231,239) then '''n' ''' + '+ @ name +' + ''''' ''' -- nvarchar, nchar when @ xtype = 61 Then ''' + convert (char (23), '+ @ name +', 121) + ''' -- datetime when @ xtype = 58 Then ''' + convert (char (16), '+ @ name +', 120) + ''' -- smalldatetime when @ xtype = 36 then''' ''' + convert (char (36 ), '+ @ name +') + ''' -- uniqueidentifier else @ name end fetch next from syscolumns_cursor into @ name, @ xtype end close syscolumns_cursor deallocate syscolumns_cursor set @ SQL = 'set nocount on select ''insert' + @ tablename + '(' + @ column + ') values (''as ''--'', '+ @ columndata + ','')' 'From' + @ tablename print '--' + @ SQL exec (@ SQL) if @ ident is not null print 'set identity_insert '+ @ tablename + 'off' go: exec uspoutputdata your table name gets the statement for exporting data, but image, text, the ntext and SQL _variant columns do not appear in the current statement and will be improved later.