存储过程添加多条记录

Question

用循环产生数字1101,1102...1118,1201,1202...1218这是一组。
              2101,2102...2118,2201,2202...2218这是第二组
              .
              .
              6101,6102...6118,6201,6202...2218
    自段就二个一个ID  另一个就是存储这个的，用循环怎么做。还有什么好办法吗？

Answer 1

create table testTab(id int primary key identity(1,1),id2 int)Godeclare @int int
set @int=100
while @int<=200
begin
insert into testTab values (@int)
set @int=@int+1
end/*
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
*/Go
select * from testTab/*
1 100
2 101
3 102
4 103
5 104
6 105
7 106
8 107
9 108
10 109
11 110
12 111
13 112
14 113
15 114
16 115
17 116
18 117
19 118
20 119
21 120
22 121
23 122
24 123
25 124
26 125
27 126
28 127
29 128
30 129
31 130
32 131
33 132
34 133
35 134
36 135
37 136
38 137
39 138
40 139
41 140
42 141
43 142
44 143
45 144
46 145
47 146
48 147
49 148
50 149
51 150
52 151
53 152
54 153
55 154
56 155
57 156
58 157
59 158
60 159
61 160
62 161
63 162
64 163
65 164
66 165
67 166
68 167
69 168
70 169
71 170
72 171
73 172
74 173
75 174
76 175
77 176
78 177
79 178
80 179
81 180
82 181
83 182
84 183
85 184
86 185
87 186
88 187
89 188
90 189
91 190
92 191
93 192
94 193
95 194
96 195
97 196
98 197
99 198
100 199
101 200
*/

Answer 2

declare @tb(id int,allid varchar(1000))
declare @i int,@j int,@s1 varchar(1000),@s2 varchar(1000)
select @i = 1
while @i<=6
begin
 select @j = 1,@s1 = '',@s2=''
 while @j<=18
 begin
  select @s1 = @s1+rtrim(@i*1000+100+@j),@s2=@s2+rtrim(@i*1000+200+@j)
  select @j = @j +1
 end
 insert @tb select @i,@s1+@s2
 select @i = @i +1
end
select * from @tb

Answer 3

declare @tb table(id int,allid varchar(1000))
declare @i int,@j int,@s1 varchar(1000),@s2 varchar(1000)
select @i = 1
while @i<=6
begin
 select @j = 1,@s1 = '',@s2=''
 while @j<=18
 begin
  select @s1 = @s1+','+rtrim(@i*1000+100+@j),@s2=@s2+','+rtrim(@i*1000+200+@j)
  select @j = @j +1
 end
 set @s1 = right(@s1,len(@s1)-1)
 insert @tb select @i,@s1+@s2
 select @i = @i +1
end
select * from @t
/*
bid          allid                                                                                                                                                                                                                                                            
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1115,1116,1117,1118,1201,1202,1203,1204,1205,1206,1207,1208,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218
2           2101,2102,2103,2104,2105,2106,2107,2108,2109,2110,2111,2112,2113,2114,2115,2116,2117,2118,2201,2202,2203,2204,2205,2206,2207,2208,2209,2210,2211,2212,2213,2214,2215,2216,2217,2218
3           3101,3102,3103,3104,3105,3106,3107,3108,3109,3110,3111,3112,3113,3114,3115,3116,3117,3118,3201,3202,3203,3204,3205,3206,3207,3208,3209,3210,3211,3212,3213,3214,3215,3216,3217,3218
4           4101,4102,4103,4104,4105,4106,4107,4108,4109,4110,4111,4112,4113,4114,4115,4116,4117,4118,4201,4202,4203,4204,4205,4206,4207,4208,4209,4210,4211,4212,4213,4214,4215,4216,4217,4218
5           5101,5102,5103,5104,5105,5106,5107,5108,5109,5110,5111,5112,5113,5114,5115,5116,5117,5118,5201,5202,5203,5204,5205,5206,5207,5208,5209,5210,5211,5212,5213,5214,5215,5216,5217,5218
6           6101,6102,6103,6104,6105,6106,6107,6108,6109,6110,6111,6112,6113,6114,6115,6116,6117,6118,6201,6202,6203,6204,6205,6206,6207,6208,6209,6210,6211,6212,6213,6214,6215,6216,6217,6218
*/