将数据库表中数据生成Insert SQL语气的存储过程
1
set
ANSI_NULLS
ON
2
set
QUOTED_IDENTIFIER
ON
3
go
4
5
6
Create
proc
[
dbo
]
.
[Get
_insert_sql
]
7(
8
@tablename
varchar
(
256
)
9 ,
@where
varchar
(
8000
)
=
null
10 ,
@orderby
varchar
(
8000
)
=
null
11 )
12
as
13
begin
14
set
nocount
on
15
declare
@sqlstr
varchar
(
8000
)
16
declare
@sqlstr1
varchar
(
8000
)
17
declare
@sqlstr2
varchar
(
8000
)
18
19
SELECT
'
/*------------------table:
'
+
@tablename
+
'
--------*/
'
20
21
IF
OBJECTPROPERTY
(
OBJECT_ID
(
@tablename
),
'
TableHasIdentity
'
)
=
1
22
BEGIN
23
SELECT
'
SET IDENTITY_INSERT
'
+
@tablename
+
'
ON
'
24
SELECT
'
GO
'
25
END
26
--
set @where=replace(@where,'''','''''')
27
28
select
@sqlstr
=
'
select
''
INSERT INTO
'
+
@tablename
29
select
@sqlstr1
=
''
30
select
@sqlstr2
=
'
(
'
31
select
@sqlstr1
=
'
VALUES (
''
+
'
32
select
@sqlstr1
=
@sqlstr1
+
col
+
'
+
''
,
''
+
'
,
@sqlstr2
=
@sqlstr2
+
name
+
'
,
'
from
(
select
case
33
--
when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
34
when
a.xtype
=
104
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(1),
'
+
a.name
+
'
)
'
+
'
end
'
35
when
a.xtype
=
175
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
36
when
a.xtype
=
36
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
37
when
a.xtype
=
35
or
a.xtype
=
99
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(convert(varchar(8000),
'
+
a.name
+
'
),
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
38
when
a.xtype
=
61
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,101)
'
+
'
+
'''''''''
+
'
end
'
39
when
a.xtype
=
106
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(
'
+
convert
(
varchar
(
4
),a.xprec
+
2
)
+
'
),
'
+
a.name
+
'
)
'
+
'
end
'
40
when
a.xtype
=
62
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,2)
'
+
'
end
'
41
when
a.xtype
=
56
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(11),
'
+
a.name
+
'
)
'
+
'
end
'
42
when
a.xtype
=
60
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(22),
'
+
a.name
+
'
)
'
+
'
end
'
43
when
a.xtype
=
239
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
44
when
a.xtype
=
108
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(
'
+
convert
(
varchar
(
4
),a.xprec
+
2
)
+
'
),
'
+
a.name
+
'
)
'
+
'
end
'
45
when
a.xtype
=
231
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''
N
''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
46
when
a.xtype
=
59
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,2)
'
+
'
end
'
47
when
a.xtype
=
58
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,101)
'
+
'
+
'''''''''
+
'
end
'
48
when
a.xtype
=
52
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(12),
'
+
a.name
+
'
)
'
+
'
end
'
49
when
a.xtype
=
122
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(22),
'
+
a.name
+
'
)
'
+
'
end
'
50
when
a.xtype
=
48
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(6),
'
+
a.name
+
'
)
'
+
'
end
'
51
--
when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
52
when
a.xtype
=
167
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
53
else
'''
NULL
'''
54
end
as
col,a.colid,a.name
55
from
syscolumns a
where
a.id
=
object_id
(
@tablename
)
and
a.xtype
<>
189
and
a.xtype
<>
34
and
a.xtype
<>
35
56 )t
order
by
colid
57
58
select
@sqlstr
=
@sqlstr
+left
(
@sqlstr2
,
len
(
@sqlstr2
)
-
1
)
+
'
)
'
+left
(
@sqlstr1
,
len
(
@sqlstr1
)
-
3
)
+
'
)
''
+
''''
from
'
+
@tablename
59
if
@where
is
not
null
60
select
@sqlstr
=
@sqlstr
+
'
where
'
+
@where
61
if
@orderby
is
not
null
62
select
@sqlstr
=
@sqlstr
+
'
order by
'
+
@orderby
63
64
--
print @sqlstr
65
exec
(
@sqlstr
)
66
67
SELECT
'
GO
'
68
69
IF
OBJECTPROPERTY
(
OBJECT_ID
(
@tablename
),
'
TableHasIdentity
'
)
=
1
70
BEGIN
71
SELECT
'
SET IDENTITY_INSERT
'
+
@tablename
+
'
OFF
'
72
SELECT
'
GO
'
73
SELECT
'
'
74
END
75
76
set
nocount
off
77
end
78
79
80
set
ANSI_NULLS
ON
2
set
QUOTED_IDENTIFIER
ON
3
go
4
5
6
Create
proc
[
dbo
]
.
[Get
_insert_sql
]
7
(8
@tablename
varchar
(
256
)9
,
@where
varchar
(
8000
)
=
null
10
,
@orderby
varchar
(
8000
)
=
null
11
)12
as
13
begin
14
set
nocount
on
15
declare
@sqlstr
varchar
(
8000
)16
declare
@sqlstr1
varchar
(
8000
)17
declare
@sqlstr2
varchar
(
8000
)18
19
SELECT
'
/*------------------table:
'
+
@tablename
+
'
--------*/
'
20
21
IF
OBJECTPROPERTY
(
OBJECT_ID
(
@tablename
),
'
TableHasIdentity
'
)
=
1
22
BEGIN
23
SELECT
'
SET IDENTITY_INSERT
'
+
@tablename
+
'
ON
'
24
SELECT
'
GO
'
25
END
26
--
set @where=replace(@where,'''','''''')
27
28
select
@sqlstr
=
'
select
''
INSERT INTO
'
+
@tablename
29
select
@sqlstr1
=
''
30
select
@sqlstr2
=
'
(
'
31
select
@sqlstr1
=
'
VALUES (
''
+
'
32
select
@sqlstr1
=
@sqlstr1
+
col
+
'
+
''
,
''
+
'
,
@sqlstr2
=
@sqlstr2
+
name
+
'
,
'
from
(
select
case
33
--
when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
34
when
a.xtype
=
104
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(1),
'
+
a.name
+
'
)
'
+
'
end
'
35
when
a.xtype
=
175
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
36
when
a.xtype
=
36
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
37
when
a.xtype
=
35
or
a.xtype
=
99
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(convert(varchar(8000),
'
+
a.name
+
'
),
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
38
when
a.xtype
=
61
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,101)
'
+
'
+
'''''''''
+
'
end
'
39
when
a.xtype
=
106
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(
'
+
convert
(
varchar
(
4
),a.xprec
+
2
)
+
'
),
'
+
a.name
+
'
)
'
+
'
end
'
40
when
a.xtype
=
62
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,2)
'
+
'
end
'
41
when
a.xtype
=
56
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(11),
'
+
a.name
+
'
)
'
+
'
end
'
42
when
a.xtype
=
60
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(22),
'
+
a.name
+
'
)
'
+
'
end
'
43
when
a.xtype
=
239
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
44
when
a.xtype
=
108
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(
'
+
convert
(
varchar
(
4
),a.xprec
+
2
)
+
'
),
'
+
a.name
+
'
)
'
+
'
end
'
45
when
a.xtype
=
231
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''
N
''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
46
when
a.xtype
=
59
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,2)
'
+
'
end
'
47
when
a.xtype
=
58
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
convert(varchar(23),
'
+
a.name
+
'
,101)
'
+
'
+
'''''''''
+
'
end
'
48
when
a.xtype
=
52
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(12),
'
+
a.name
+
'
)
'
+
'
end
'
49
when
a.xtype
=
122
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(22),
'
+
a.name
+
'
)
'
+
'
end
'
50
when
a.xtype
=
48
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'
convert(varchar(6),
'
+
a.name
+
'
)
'
+
'
end
'
51
--
when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
52
when
a.xtype
=
167
then
'
case when
'
+
a.name
+
'
is null then
''
NULL
''
else
'
+
'''''''''
+
'
+
'
replace(
'
+
a.name
+
'
,
''''''''
,
''''''''''''
)
'
+
'
+
'''''''''
+
'
end
'
53
else
'''
NULL
'''
54
end
as
col,a.colid,a.name55
from
syscolumns a
where
a.id
=
object_id
(
@tablename
)
and
a.xtype
<>
189
and
a.xtype
<>
34
and
a.xtype
<>
35
56
)t
order
by
colid57
58
select
@sqlstr
=
@sqlstr
+left
(
@sqlstr2
,
len
(
@sqlstr2
)
-
1
)
+
'
)
'
+left
(
@sqlstr1
,
len
(
@sqlstr1
)
-
3
)
+
'
)
''
+
''''
from
'
+
@tablename
59
if
@where
is
not
null
60
select
@sqlstr
=
@sqlstr
+
'
where
'
+
@where
61
if
@orderby
is
not
null
62
select
@sqlstr
=
@sqlstr
+
'
order by
'
+
@orderby
63
64
--
print @sqlstr
65
exec
(
@sqlstr
)66
67
SELECT
'
GO
'
68
69
IF
OBJECTPROPERTY
(
OBJECT_ID
(
@tablename
),
'
TableHasIdentity
'
)
=
1
70
BEGIN
71
SELECT
'
SET IDENTITY_INSERT
'
+
@tablename
+
'
OFF
'
72
SELECT
'
GO
'
73
SELECT
'
'
74
END
75
76
set
nocount
off
77
end
78
79
80
32万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
