你肯定有过这样的烦恼，同样的表，不同的数据库，加入你不能执行select insert
那么你肯定需要一条这样的存储过程，之需要传入表明，就会给你生成数据的插入语句。
当然数据表数量太大，你将最好用别的方式
Create
proc
[
dbo
]
.
[
spGenInsertSQL
]
(
@tablename
varchar
(
256
))
as
begin
declare
@sql
varchar
(
8000
)
declare
@sqlValues
varchar
(
8000
)
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 '''''''''+convert(char(23),'+name+',121)+''''''''' --datetime
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
=
'
select
''
INSERT INTO [
'
+
@tablename
+
'
]
'
+
left
(
@sql
,
len
(
@sql
)
-
1
)
+
'
)
'
+
left
(
@sqlValues
,
len
(
@sqlValues
)
-
4
)
+
'
)
''
from
'
+
@tablename
print
@sql
exec
(
@sql
)
end
最后的结果：
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0002CA83-AF2F-4D8F-A345-33CA1CC7CF3C','任务调度系统',18,'2013-01-02 21:42:30.013','',NULL,'2013-01-02 21:42:30.013')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('0004A6F3-EC28-4D1F-BA40-0FC4B2218C92','任务调度系统',18,'2013-07-09 19:36:00.060','',NULL,'2013-07-09 19:36:00.060')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('00094D35-7B51-4EA3-871E-CE17E293B157','任务调度系统',18,'2013-05-16 15:21:20.070','',NULL,'2013-05-16 15:21:20.070')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('000BFBB0-B37D-4D6E-9FA2-3069D4F18F84','任务调度系统',18,'2013-04-11 11:41:50.030','',NULL,'2013-04-11 11:41:50.030')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('000C2CBC-E358-4469-BC2C-04F4DDCD72CD','任务调度系统',18,'2013-05-06 16:07:00.037','',NULL,'2013-05-06 16:07:00.037')
INSERT INTO [SysSample] ([Id],[Name],[Age],[Bir],[Photo],[Note],[CreateTime]) values ('000CB795-40EC-4783-B7A4-8D298DF63B70','任务调度系统',18,'2013-01-23 20:52:30.030','',NULL,'2013-01-23 20:52:30.030')
