Generating SQL statements for data in a table

In the development process, we often need to transfer the data in the table, if the same machine, you can use the SQL comes with the import data, but if you want all the data to generate executable SQL statements , it is the most portability.
The first step is to design a stored procedure. Specific as follows:

CREATEPROCEDUREDbo. Uspoutputdata
@tablenamesysname
as
Declare@columnvarchar( +)
Declare@columndatavarchar( +)
Declare@sqlvarchar(4000)
Declare@xtypetinyint
Declare@namesysname
Declare@objectIdint
Declare@objectnamesysname
Declare @identint

SetNocount on
Set@objectId=object_id(@tablename)

if@objectId isNULL--To judge the existence of the image
begin
Print'The object is not exists'
return
End
Set@objectname=RTrim(object_name(@objectId))

if@objectname isNULLorcharindex(@objectname,@tablename)=0--This judgment is not strict
begin
Print'object not in current database'
return
End

ifObjectProperty(@objectId,'istable')<> 1--judge whether the image is a table
begin
Print'The object is not table'
return
End

Select@ident=Status&0x80 fromsyscolumnswhereId=@objectid andStatus&0x80=0x80

if@ident is notNULL
Print'SET Identity_insert'+@TableName+' on'

DeclareSyscolumns_cursorcursor

forSelectC.name,c.xtype fromsyscolumns CwhereC.id=@objectidOrder byC.colid

OpenSyscolumns_cursor
Set@column="'
Set@columndata="'
FetchNext fromSyscolumns_cursor into@name,@xtype

while@ @fetch_status<>-1
begin
if@ @fetch_status<>-2
begin
if@xtype notinch(189, the, *, About,98)--timestamp do not need processing, image,text,ntext,sql_variant temporarily do not handle

begin
Set@column=@column+ Case whenLen(@column)=0 Then"'Else','End+@name

Set@columndata=@columndata+ Case whenLen(@columndata)=0 Then "'Else',"',"','
End

+ Case when@xtypeinch(167,175) Then "" "+'+@name+'+ "" "--Varchar,char
when@xtypeinch(231,239) Then" "N" " "+'+@name+'+ "" "--Nvarchar,nchar
when@xtype= A Then "" "+convert (char),'+@name+', 121) + "" "--datetime
when@xtype= - Then "" "+convert (char),'+@name+', +) + "" "--smalldatetime
when@xtype= $ Then "" "+convert (char),'+@name+')+ "" "--uniqueidentifier
Else@nameEnd

End

End

FetchNext fromSyscolumns_cursor into@name,@xtype

End

CloseSyscolumns_cursor
deallocateSyscolumns_cursor

Set@sql='SET NOCOUNT ON select"'Insert'+@tablename+'('+@column+') VALUES ("' as"'--"','+@columndata+',"')"' from'+@tablename

Print'--'+@sql
exec(@sql)

if@ident is notNULL
Print'SET Identity_insert'+@TableName+'OFF'

GO

execUspoutputdata TableName[Table name]

The query results are as follows:

InsertT_user_title (F_id,f_titlename,f_remark,f_status,f_edittime,f_institutionid)Values(101,'Software engineer','engaged in ASP. NET Software Development',1,'2007-12-26 10:26:43.000',101)
InsertT_user_title (F_id,f_titlename,f_remark,f_status,f_edittime,f_institutionid)Values(201,'Sales Staff','engaged in software sales',1,'2007-12-26 10:26:29.000',101)
InsertT_user_title (F_id,f_titlename,f_remark,f_status,f_edittime,f_institutionid)Values(301,'SFGSDFG','ASDFASDF',3,'2007-12-25 18:21:48.000',101)

Tips:
After doing so, you might get a table-based data. To further generate the SQL statements that are available, simply set up the SQL.
Open the Query window, right-click on the page-----There is an option "Save results to"-----Select "Show results in text Format"
The results are as follows: