Use SQL to generate data insert SQL scripts

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 the 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 strict

Begin

Print 'object not in current database'

Return

End

If objectproperty (@ objectid, 'istable') <> 1 -- determines whether the object is a 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 will not be processed for the moment.

Begin

Set @ column = @ 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 ''' + '+ @ 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

End

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 brand