SQL Server Low version build Insert Script

--A stored procedure that generates SQL scripts for table data

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--Determines whether an object exists
Begin
print ' The object Not EXISTS '
Return
End
Set @objectname =rtrim (object_name (@objectId))

If @objectname is null or CHARINDEX (@objectname, @tablename) =0--This judgment is not tight
Begin
Print ' object not in current database '
Return
End

If OBJECTPROPERTY (@objectId, ' istable ') < > 1--Determine if the object is a table
Begin
print ' The object is not table '
Return
End

Select @ident =status&0x80 from syscolumns where [email protected] and status&0x80=0x80

If @ident is not null
print ' SET identity_insert ' [email protected]+ ' on '

DECLARE syscolumns_cursor cursor

For select C.name,c.xtype from syscolumns c where [e-mail protected] ORDER by C.colid

Open Syscolumns_cursor
Set @column = ' '
Set @columndata = ' '
FETCH NEXT from Syscolumns_cursor to @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 temporarily does not process

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 into (167,175) then "" "" + "[email protected]+ ' + '" "" "--varchar,char
When @xtype in (231,239) then "N" "+" [email protected]+ ' + ' "" "--nvarchar,nchar
When @xtype =61 and then "" "+convert (char), ' [email protected]+ ', 121] + '" ""--datetime
When @xtype =58 and then "" +convert (char), ' [email protected]+ ', + + '] + ' "'--smalldatetime
When @xtype =36 and then "" "+convert (char), ' [email protected]+ ') + '" "'--uniqueidentifier
else @name End

End

End

FETCH NEXT from Syscolumns_cursor to @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 '

GO

EXEC uspoutputdata your table name

SQL Server Low version build Insert Script