在开发过程中,经常需要我们对表中的数据进行转移,如果在同台机器,可以使用SQL自带的导入数据,但是如果想让所有的数据生成可执行的SQL语句,它的移植性最强了。
首先要设计一个存储过程。具体如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE PROCEDURE dbo.UspOutputData
@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 ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set nocount
on
set @objectId=object_id(
@tablename)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @objectId is null -- 判斷對象是否存在
begin
print 'The object not exists'
return
end
set @objectname=rtrim(
object_name(
@objectId))
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @objectname is null or charindex(
@objectname,
@tablename)
=0 --此判断不严密
begin
print 'object not in current database'
return
end ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if OBJECTPROPERTY(
@objectId,
'IsTable')
< > 1 -- 判斷對象是否是table
begin
print 'The object is not table'
return
end ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
select @ident=status
&0x80 from syscolumns
where id
=@objectid and status
&0x80=0x80 ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' ON' ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare syscolumns_cursor
cursor![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
for select c.name,c.xtype
from syscolumns c
where c.id
=@objectid order by c.colid
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
open syscolumns_cursor
set @column=''
set @columndata=''
fetch next from syscolumns_cursor
into @name,
@xtype ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
while @@fetch_status < >-1
begin
if @@fetch_status < >-2
begin
if @xtype not in(
189,
34,
35,
99,
98)
--timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
set @column=@column+case when len(
@column)
=0 then'' else ','end+@name ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @columndata=@columndata+case when len(
@columndata)
=0 then '' else ','','','
end ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
+case when @xtype in(
167,
175)
then '''''''''+'+@name+'+''''''''' --varchar,char
when @xtype in(
231,
239)
then '''N''''''+'+@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 ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
fetch next from syscolumns_cursor
into @name,
@xtype ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
close syscolumns_cursor
deallocate syscolumns_cursor
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print '--'+@sql
exec(
@sql)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if @ident is not null
print 'SET IDENTITY_INSERT '+@TableName+' OFF' ![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
exec UspOutputData tableName
[表名]![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
查询结果如下:
insert T_user_title(F_ID,F_TitleName,F_Remark,F_Status,F_EditTime,F_InstitutionId)
values(
101 ,
'软件工程师' ,
'从事ASP.NET软件研发',
1,
'2007-12-26 10:26:43.000',
101)
insert T_user_title(F_ID,F_TitleName,F_Remark,F_Status,F_EditTime,F_InstitutionId)
values(
201 ,
'销售人员' ,
'从事软件销售' ,
1 ,
'2007-12-26 10:26:29.000',
101 )
insert T_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 )
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
提示:
这样执行之后,可能你得到的是基于表格内的数据。为了进一步生成可用的SQL语句,只要对SQL简单的进行设置就可以了。
打开查询窗口,右击页面-----》有一选项【将结果保存到】-----》选择【以文本格式显示结果】
得到的结果就如下: