看到一很好的SQL代码
转载:http://topic.csdn.net/u/20080310/09/f1c5c8d1-e60b-48ad-8068-fa5a58e59788.html
其功能是：将表中的数据生成SQL脚本，在查询分析器中执行这些脚本后自动将数据导入到SQL Server中
CREATE
PROCEDURE
dbo.OutputData
@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
set
nocount
on
set
@objectId
=
object_id
(
@tablename
)
if
@objectId
is
null
--
判断对象是否存在
begin
print
@tablename
+
'
对象不存在
'
return
end
set
@objectname
=
rtrim
(
object_name
(
@objectId
))
if
@objectname
is
null
or
charindex
(
@objectname
,
@tablename
)
=
0
begin
print
@tablename
+
'
对象不在当前数据库中
'
return
end
if
OBJECTPROPERTY
(
@objectId
,
'
IsTable
'
)
<
>
1
--
判断对象是否是表
begin
print
@tablename
+
'
对象不是表
'
return
end
select
@ident
=
status
&
0x80
from
syscolumns
where
id
=
@objectid
and
status
&
0x80
=
0x80
if
@ident
is
not
null
print
'
SET IDENTITY_INSERT
'
+
@TableName
+
'
ON
'
--
定义游标，循环取数据并生成Insert语句
declare
syscolumns_cursor
cursor
for
select
c.name,c.xtype
from
syscolumns c
where
c.id
=
@objectid
order
by
c.colid
--
打开游标
open
syscolumns_cursor
set
@column
=
''
set
@columndata
=
''
fetch
next
from
syscolumns_cursor
into
@name
,
@xtype
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 暂时不处理
begin
set
@column
=
@column
+
case
when
len
(
@column
)
=
0
then
''
else
'
,
'
end
+
@name
set
@columndata
=
@columndata
+
case
when
len
(
@columndata
)
=
0
then
''
else
'
,
''
,
''
,
'
end
+
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
end
end
fetch
next
from
syscolumns_cursor
into
@name
,
@xtype
end
close
syscolumns_cursor
deallocate
syscolumns_cursor
set
@sql
=
'
set nocount on select
''
insert
'
+
@tablename
+
'
(
'
+
@column
+
'
) values(
''
as
''
--
''
,
'
+
@columndata
+
'
,
''
)
''
from
'
+
@tablename
print
'
--
'
+
@sql
exec
(
@sql
)
if
@ident
is
not
null
print
'
SET IDENTITY_INSERT
'
+
@TableName
+
'
OFF
'
调用时
exec
OutputData
'
myuser
'
其中myUser中当前数据库中存在的表
转载于:https://www.cnblogs.com/HappyQQ/archive/2008/03/11/1101295.html
