SQL script Generator for copying table data developed by SQL Server

You can use the "generate SQL script" tool that comes with SQL Server 2000 to generate SQL scripts for creating tables, views, and stored procedures. So can we generate SQL scripts for the data in the table and automatically import the data to SQL Server after executing these scripts in the query analyzer? The answer is yes.

The following stored procedure is written by a senior user. the last name of the senior user is unknown, but the SQL Server community occasionally shows the immortal work.

Create procedure DBO. outputdata
@ 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 @ tablename + 'object does not exist'
Return
End

Set @ objectname = rtrim (object_name (@ objectid ))
If @ objectname is null or charindex (@ objectname, @ tablename) = 0
Begin
Print @ tablename + 'the object is not in the current database'
Return
End

If objectproperty (@ objectid, 'istable') <> 1 -- determines whether the object is a table.
Begin
Print @ tablename + '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'

-- Define a cursor, fetch data cyclically and generate an insert statement
Declare syscolumns_cursor cursor
Select C. Name, C. xtype from syscolumns C
Where C. ID = @ objectid
Order by C. colid

-- Open the cursor
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'

During the call, exec outputdata 'myuser' contains the tables in the current database of myuser.