表数据生成插入脚本(转)
1
2
/* Usage:
3
exec DataAsInsCommand 'sys$serialcodetype'
4
*/
5
6
IF OBJECT_ID('DataAsInsCommand') IS NOT NULL DROP PROC DataAsInsCommand
7
GO
8
SET QUOTED_IDENTIFIER OFF
9
GO
10
CREATE PROC DataAsInsCommand (
11
@TableList varchar (200))
12
AS
13
SET NOCOUNT ON
14
DECLARE @position int, @exec_str varchar (2000), @TableName varchar (30)
15
DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity tinyint
16
SELECT @TableList = @TableList + ','
17
SELECT @IsIdentity = 0
18
SELECT @position = PATINDEX('%,%', @TableList)
19
WHILE (@position <> 0)
20
BEGIN
21
SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
22
SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%', @TableList), '')
23
SELECT @position = PATINDEX('%,%', @TableList)
24
25
SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR '
26
+ 'SELECT name, xtype, status FROM syscolumns WHERE id = object_id("'
27
+ @TableName + '")'
28
EXEC (@exec_str)
29
30
OPEN fetch_cursor
31
FETCH fetch_cursor INTO @name, @xtype, @status
32
IF (@status & 0x80) <> 0
33
BEGIN
34
SELECT @IsIdentity = 1
35
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
36
SELECT 'GO'
37
END
38
SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES (' + "
39
40
--text or ntext
41
IF (@xtype = 35) OR (@xtype = 99)
42
SELECT @exec_str = @exec_str + '''"None yet"'''
43
ELSE
44
45
--image
46
IF (@xtype = 34)
47
SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
48
ELSE
49
50
--smalldatetime or datetime
51
IF (@xtype = 58) OR (@xtype = 61)
52
SELECT @exec_str = @exec_str + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"'''
53
ELSE
54
55
--varchar or char or nvarchar or nchar
56
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
57
SELECT @exec_str = @exec_str + '''"'' + ' + @name + ' + ''"'''
58
ELSE
59
60
--uniqueidentifier
61
IF (@xtype = 36)
62
SELECT @exec_str = @exec_str + ' + ''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"'''
63
ELSE
64
65
--binary or varbinary
66
IF (@xtype = 173) OR (@xtype = 165)
67
SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
68
ELSE
69
70
SELECT @exec_str = @exec_str + 'ISNULL(CONVERT(varchar,' + @name + '), "null")'
71
72
WHILE @@FETCH_STATUS <> -1
73
BEGIN
74
FETCH fetch_cursor INTO @name, @xtype, @status
75
IF (@@FETCH_STATUS = -1) BREAK
76
IF (@status & 0x80) <> 0
77
BEGIN
78
SELECT @IsIdentity = 1
79
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
80
SELECT 'GO'
81
END
82
83
--text or ntext
84
IF (@xtype = 35) OR (@xtype = 99)
85
SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"None yet"'''
86
ELSE
87
88
--image
89
IF (@xtype = 34)
90
SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0xFFFFFFFF' + '"'
91
ELSE
92
93
--smalldatetime or datetime
94
IF (@xtype = 58) OR (@xtype = 61)
95
SELECT @exec_str = @exec_str + ' + ","' + ' + ''"'' + ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"'''
96
ELSE
97
98
--varchar or char or nvarchar or nchar
99
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype = 239)
100
SELECT @exec_str = @exec_str + ' + '',''' + ' + ''"'' + ' + @name + ' + ''"'''
101
ELSE
102
103
--uniqueidentifier
104
IF (@xtype = 36)
105
SELECT @exec_str = @exec_str + ' + ","' + ' + ''"'' + ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"'''
106
ELSE
107
108
--binary or varbinary
109
IF (@xtype = 173) OR (@xtype = 165)
110
SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0x0' + '"'
111
ELSE
112
113
SELECT @exec_str = @exec_str + ' + ","' + ' + ISNULL(CONVERT(varchar,' + @name + '), "null")'
114
END
115
116
CLOSE fetch_cursor
117
DEALLOCATE fetch_cursor
118
119
SELECT @exec_str = @exec_str + '+ ")" FROM ' + @TableName
120
SELECT @exec_str
121
EXEC(@exec_str)
122
SELECT 'GO'
123
124
IF @IsIdentity = 1
125
BEGIN
126
SELECT @IsIdentity = 0
127
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
128
SELECT 'GO'
129
END
130
END
131
GO
132![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/Images/OutliningIndicators/None.gif)
60
![](/Images/OutliningIndicators/None.gif)
61
![](/Images/OutliningIndicators/None.gif)
62
![](/Images/OutliningIndicators/None.gif)
63
![](/Images/OutliningIndicators/None.gif)
64
![](/Images/OutliningIndicators/None.gif)
65
![](/Images/OutliningIndicators/None.gif)
66
![](/Images/OutliningIndicators/None.gif)
67
![](/Images/OutliningIndicators/None.gif)
68
![](/Images/OutliningIndicators/None.gif)
69
![](/Images/OutliningIndicators/None.gif)
70
![](/Images/OutliningIndicators/None.gif)
71
![](/Images/OutliningIndicators/None.gif)
72
![](/Images/OutliningIndicators/None.gif)
73
![](/Images/OutliningIndicators/None.gif)
74
![](/Images/OutliningIndicators/None.gif)
75
![](/Images/OutliningIndicators/None.gif)
76
![](/Images/OutliningIndicators/None.gif)
77
![](/Images/OutliningIndicators/None.gif)
78
![](/Images/OutliningIndicators/None.gif)
79
![](/Images/OutliningIndicators/None.gif)
80
![](/Images/OutliningIndicators/None.gif)
81
![](/Images/OutliningIndicators/None.gif)
82
![](/Images/OutliningIndicators/None.gif)
83
![](/Images/OutliningIndicators/None.gif)
84
![](/Images/OutliningIndicators/None.gif)
85
![](/Images/OutliningIndicators/None.gif)
86
![](/Images/OutliningIndicators/None.gif)
87
![](/Images/OutliningIndicators/None.gif)
88
![](/Images/OutliningIndicators/None.gif)
89
![](/Images/OutliningIndicators/None.gif)
90
![](/Images/OutliningIndicators/None.gif)
91
![](/Images/OutliningIndicators/None.gif)
92
![](/Images/OutliningIndicators/None.gif)
93
![](/Images/OutliningIndicators/None.gif)
94
![](/Images/OutliningIndicators/None.gif)
95
![](/Images/OutliningIndicators/None.gif)
96
![](/Images/OutliningIndicators/None.gif)
97
![](/Images/OutliningIndicators/None.gif)
98
![](/Images/OutliningIndicators/None.gif)
99
![](/Images/OutliningIndicators/None.gif)
100
![](/Images/OutliningIndicators/None.gif)
101
![](/Images/OutliningIndicators/None.gif)
102
![](/Images/OutliningIndicators/None.gif)
103
![](/Images/OutliningIndicators/None.gif)
104
![](/Images/OutliningIndicators/None.gif)
105
![](/Images/OutliningIndicators/None.gif)
106
![](/Images/OutliningIndicators/None.gif)
107
![](/Images/OutliningIndicators/None.gif)
108
![](/Images/OutliningIndicators/None.gif)
109
![](/Images/OutliningIndicators/None.gif)
110
![](/Images/OutliningIndicators/None.gif)
111
![](/Images/OutliningIndicators/None.gif)
112
![](/Images/OutliningIndicators/None.gif)
113
![](/Images/OutliningIndicators/None.gif)
114
![](/Images/OutliningIndicators/None.gif)
115
![](/Images/OutliningIndicators/None.gif)
116
![](/Images/OutliningIndicators/None.gif)
117
![](/Images/OutliningIndicators/None.gif)
118
![](/Images/OutliningIndicators/None.gif)
119
![](/Images/OutliningIndicators/None.gif)
120
![](/Images/OutliningIndicators/None.gif)
121
![](/Images/OutliningIndicators/None.gif)
122
![](/Images/OutliningIndicators/None.gif)
123
![](/Images/OutliningIndicators/None.gif)
124
![](/Images/OutliningIndicators/None.gif)
125
![](/Images/OutliningIndicators/None.gif)
126
![](/Images/OutliningIndicators/None.gif)
127
![](/Images/OutliningIndicators/None.gif)
128
![](/Images/OutliningIndicators/None.gif)
129
![](/Images/OutliningIndicators/None.gif)
130
![](/Images/OutliningIndicators/None.gif)
131
![](/Images/OutliningIndicators/None.gif)
132
![](/Images/OutliningIndicators/None.gif)