在开发过程中,经常需要我们对表中的数据进行转移,如果在同台机器,可以使用SQL自带的导入数据,但是如果想让所有的数据生成可执行的SQL语句,它的移植性最强了。
首先要设计一个存储过程。具体如下:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
CREATE
PROCEDURE dbo.UspOutputData
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
@tablename sysname
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
AS
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@column
varchar(
1000)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@columndata
varchar(
1000)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@sql
varchar(
4000)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@xtype
tinyint
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@name sysname
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@objectId
int
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@objectname sysname
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
declare
@ident
int
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set nocount
on
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set
@objectId
=
object_id(
@tablename)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@objectId
is
null
--
判斷對象是否存在
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print
'
The object not exists
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set
@objectname
=
rtrim(
object_name(
@objectId))
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@objectname
is
null
or
charindex(
@objectname,
@tablename)
=
0
--
此判断不严密
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print
'
object not in current database
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
OBJECTPROPERTY(
@objectId,
'
IsTable
')
<
>
1
--
判斷對象是否是table
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
print
'
The object is not table
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set
@column
=
''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
set
@columndata
=
''
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
fetch
next
from syscolumns_cursor
into
@name,
@xtype
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
while
@@fetch_status
<
>-
1
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@@fetch_status
<
>-
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@xtype
not
in(
189,
34,
35,
99,
98)
--
timestamp不需处理,image,text,ntext,sql_variant 暂时不处理
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
begin
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
'
,
''
,
''
,
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
end
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
+
case
when
@xtype
in(
167,
175)
then
'''''''''
+
'
+
@name
+
'
+
'''''''''
--
varchar,char
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
when
@xtype
in(
231,
239)
then
'''
N
''''''
+
'
+
@name
+
'
+
'''''''''
--
nvarchar,nchar
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
when
@xtype
=
61
then
'''''''''
+convert(char(23),
'
+
@name
+
'
,121)+
'''''''''
--
datetime
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
when
@xtype
=
58
then
'''''''''
+convert(char(16),
'
+
@name
+
'
,120)+
'''''''''
--
smalldatetime
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
when
@xtype
=
36
then
'''''''''
+convert(char(36),
'
+
@name
+
'
)+
'''''''''
--
uniqueidentifier
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
exec(
@sql)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if
@ident
is
not
null
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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)
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)
![](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(
201 ,
'
销售人员
' ,
'
从事软件销售
' ,
1 ,
'
2007-12-26 10:26:29.000
',
101 )
![](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(
301 ,
'
sfgsdfg
' ,
'
asdfasdf
',
3 ,
'
2007-12-25 18:21:48.000
',
101 )
提示:
这样执行之后,可能你得到的是基于表格内的数据。为了进一步生成可用的SQL语句,只要对SQL简单的进行设置就可以了。
打开查询窗口,右击页面-----》有一选项【将结果保存到】-----》选择【以文本格式显示结果】
得到的结果就如下: