表数据生成插入脚本(转)
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
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
