因項目需要，有時要求將一個表中的記錄導(dǎo)出來，結(jié)合網(wǎng)上搜索的一些代碼，而成此文 。因時間過長已找不出原創(chuàng)作者，謹請原諒

Create procedure pGetInsertSQL (@tablename varchar(256))
---WITH ENCRYPTION
as
begin
---得到表中的SQL
?-- declare @tablename varchar(256)
?-- set @tablename = 'Design_DBPageStruct'
?--Create Table SQLtmp
?--(
?--sql varchar(4000)
?--)
---Delete? from SQLtmp

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQLtmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Create Table SQLtmp
(
sql varchar(4000)
)

? declare @sql varchar(4000)
? declare @sqlValues varchar(4000)
? set @sql =' ('
? set @sqlValues = 'values (''+'
? select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
? from
??? (select case
??????????? when xtype in (48,52,56,59,60,62,104,106,108,122,127)???????????????????????????????
???????????????? then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
??????????? when xtype in (58,61)
???????????????? then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
??????????? when xtype in (167)
???????????????? then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
??????????? when xtype in (231)
???????????????? then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
??????????? when xtype in (175)
???????????????? then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)? + '))+'''''''''+' end'
??????????? when xtype in (239)
???????????????? then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)? + '))+'''''''''+' end'
??????????? else '''NULL'''
??????????? end as Cols,name
????? from syscolumns?
????? where id = object_id(@tablename)
??? ) T
? set @sql ='insert into SQLtmp (sql) select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ');'' from '+@tablename + '? '
--? print @sql
? exec (@sql)
?
---Select sql from SQLtmp
---Select sql from SQLtmp? FOR XML AUTO
end

go


應(yīng)用方法

Create Procedure pBackInsertSQL
As
Begin
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SQLtmp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Create Table SQLtmp
(
sql varchar(4000)
)
else
Truncate Table SQLtmp

print '正在備份表' + 'Cfg_DataDictionary'
Execute pGetInsertSQL 'Cfg_DataDictionary'

print '插入中間分隔符'
Insert into SQLtmp(sql) values ('*************************************')

print '正在備份表' + 'Shuttle'
Execute pGetInsertSQL 'Shuttle'

print '插入中間分隔符'
Insert into SQLtmp(sql) values ('*************************************')

print '正在備份表' + 'Shuttle_Param'
Execute pGetInsertSQL 'Shuttle_Param'

print '插入中間分隔符'
Insert into SQLtmp(sql) values ('*************************************')

Declare @Cnt varchar(10)
Select @Cnt = convert(varchar(10),count(sql)) From SQLtmp

Print '共計備份數(shù)據(jù) '+@Cnt + '條'
End

轉(zhuǎn)載于:https://www.cnblogs.com/bigmouthz/archive/2006/10/24/538341.html

總結(jié)

以上是生活随笔為你收集整理的表中的数据导出为insert语句的简单方法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。