How to export data from a SQL Server table or generate an INSERT INTO statement [go]

Enter the table name to generate the INSERT statement

drop proc Proc_insert//delete if present
Go
Create proc Proc_insert (@tablename varchar (256))
As
Begin
SET NOCOUNT ON
DECLARE @sqlstr varchar (4000)
DECLARE @sqlstr1 varchar (4000)
DECLARE @sqlstr2 varchar (4000)
Select @sqlstr = ' select ' ' Insert ' [email protected]
Select @sqlstr1 = "
Select @sqlstr2 = ' ('
Select @sqlstr1 = ' values ' (' + ')
Select @[email protected]+col+ ' + ', ' + ', @[email protected]+name + ', ' from (Select Case
--When A.xtype =173 and then ' case ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), A.lengt h*2+2) + '), ' +a.name + ') ' + ' end '
When A.xtype =127 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (), ' +a.name + ') ' + ' end '
When A.xtype =104 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (1), ' +a.name + ') ' + ' end '
When A.xtype =175 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '
When A.xtype =61 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' "' + ' + ' convert (varchar), ' +a.name + ', 121) ' + ' + ' + ' + ' end '
When A.xtype =106 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), a.xprec+2) + '), ' +a.name + ') ' + ' end '
When A.xtype =62 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar, ' +a.name + ', 2) ' + ' end '
When A.xtype =56 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (one) ', ' +a.name + ') ' + ' end '
When A.xtype =60 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar) ', ' +a.name + ') ' + ' end '
When A.xtype =239 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '
When A.xtype =108 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), a.xprec+2) + '), ' +a.name + ') ' + ' end '
When A.xtype =231 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '
When A.xtype =59 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar, ' +a.name + ', 2) ' + ' end '
When A.xtype =58 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' "' + ' + ' convert (varchar), ' +a.name + ', 121) ' + ' + ' + ' + ' end '
When A.xtype =52 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (), ' +a.name + ') ' + ' end '
When A.xtype =122 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar) ', ' +a.name + ') ' + ' end '
When A.xtype =48 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (6), ' +a.name + ') ' + ' end '
--When A.xtype =165 and then ' case ' +a.name+ ' was null then ' null ' ' Else ' + ' convert (varchar (' +convert (varchar (4), A.lengt h*2+2) + '), ' +a.name + ') ' + ' end '
When A.xtype =167 then ' case ', ' +a.name+ ' was null then ' null ' ' Else ' + ' ' ' ' ' + ' + ' replace (' +a.name+ ', ' ' "') ' + ' + ' + ' + ' + ' + ' + ' end '
Else ' NULL '
End as Col,a.colid,a.name
From syscolumns a WHERE a.id = object_id (@tablename) and A.xtype <>189 and A.xtype <>34 and A.xtype <>35 and A.xtype <>36
) T ORDER by colid

Select @[email protected]+left (@sqlstr2, Len (@sqlstr2)-1) ' +left (@sqlstr1, Len (@sqlstr1)-3) + ') ' from ' [email Protected
--Print @sqlstr
EXEC (@sqlstr)
SET NOCOUNT OFF
End
Go

SELECT * FROM systypes

How to use:

EXEC Proc_insert your table name

Select execute mode to display results in text, and then save the running results as. SQL, plus the database scripts generated with SQL Server.

In addition, third-party tools can be used to export the data data of the food Memo field can be PowerBuilder. In database painter, choose SQL, or open the table directly, click on the generated list DataWindow, and then File->save rows as-> the menu to select SQL, The resulting SQL statement will include SQL to build the table and insert data.

The most perfect way to do this is to implement the import and export yourself, so that you can handle data transformations in all cases.

How to export data from a SQL Server table or generate an INSERT INTO statement [go]