Create a storage process for SQL Server table data (generate an insert script)

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.

 

Create procedure DBO. outputdata
@ TablenameSysname
As
Declare@ ColumnVarchar(1000)
Declare@ ColumndataVarchar(1000)
Declare@ SQLVarchar(4000)
Declare@ XtypeTinyint
Declare@ NameSysname
Declare@ ObjectidInt
Declare@ ObjectnameSysname
Declare@ IdentInt

SetNocountOn
Set@ Objectid =Object_id(@ Tablename)
If @ ObjectidIsNull-- Determine whether an object exists
Begin
Print @ Tablename +' The object does not exist.'
Return
End

Set@ Objectname =Rtrim(Object_name(@ Objectid ))
If@ ObjectnameIs null or charindex(@ Objectname, @ tablename) = 0
Begin
Print @ Tablename +' The object is not in the current database.'
Return
End

If Objectproperty(@ Objectid, 'istable ')<>1-- Determine whether an object is a table
Begin
Print @ Tablename +' The object is not a table.'
Return
End

Select@ Ident = Status & 0x80FromSyscolumnsWhereId = @ objectidAndStatus & 0x80 = 0x80

If@ IdentIsNotNull
Print'SetIdentity_insert'+ @ Tablename +'On'

-- Define a cursor, fetch data cyclically and generateInsertStatement
Declare Syscolumns_cursorCursor 
SelectC. Name, C. xtypeFromSyscolumnsC
WhereC. ID = @ objectid
OrderByC. colid

-- Open cursor
OpenSyscolumns_cursor
Set @ Column =''
Set @ Columndata =''
FetchNextFromSyscolumns_cursorInto@ Name, @ xtype
While@ Fetch_status<>-1
Begin
If@ Fetch_status<>-2
Begin
If@ XtypeNotIn(189,34, 35,99, 98)-- Timestamp No need to handle,Image, text, ntext, SQL _variantNot processed
Begin
Set@ Column = @ column +
CaseWhenLen(@ Column) = 0Then''
Else','
End+ @ Name
Set@ Columndata = @ columndata +
CaseWhenLen(@ Columndata) = 0Then''
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 = 36Then''' + Convert (char (36), '+ @ name +') + '''''''''-- Uniqueidentifier
Else@ Name
End
End
End
FetchNextFromSyscolumns_cursorInto@ Name, @ xtype
End
CloseSyscolumns_cursor
DeallocateSyscolumns_cursor

Set @ SQL = 'setNocountOnSelect''Insert'+ @ Tablename +' ('+ @ column + ')Values (''''--'', '+ @ Columndata + ','')''From'+ @ Tablename

Print'--' + @ SQL
Exec(@ SQL)

If @ ident is not null
Print 'set identity_insert '+ @ tablename +' off'

Call timeExec outputdata 'myuser'. The tables in the current database of myuser