34,837
社区成员
发帖
与我相关
我的任务
分享怎样用SQL语句批量生成测试数据
想在SQL Server 中执行SQL语句直接生成SQL语句，而不用手动添加
表BlockTable
字段：BlockNo BinNo Rank ChipCount MachineNo
生成130条记录，BlockNo从1到130
BinNo从1到10随机（可重复）
Rank与BlockNo相同也是从1到130
ChipCount全部置0
MachineNo全部为001
表BlockTable
字段：BlockNo BinNo Rank ChipCount MachineNo
生成130条记录，BlockNo从1到130
BinNo从1到10随机（可重复）
Rank与BlockNo相同也是从1到130
ChipCount全部置0
MachineNo全部为001
...全文
请发表友善的回复…
发表回复
liangyong1107 2011-08-10
- 打赏
- 举报
[Quote=引用 4 楼 fredrickhu 的回复:]
SQL code
select
cast((rand(checksum(newid()))*130) as int),
cast((rand(checksum(newid()))*10) as int),
cast((rand(checksum(newid()))*130) as int),0,'001'
from
master..spt_values
where
[……
[/Quote]
+1 学习，简单易懂
SQL code
select
cast((rand(checksum(newid()))*130) as int),
cast((rand(checksum(newid()))*10) as int),
cast((rand(checksum(newid()))*130) as int),0,'001'
from
master..spt_values
where
[……
[/Quote]
+1 学习，简单易懂
cutebear2008 2011-08-10
- 打赏
- 举报
1到130应该不重复的吧，这个方法有重复的！
[Quote=引用 5 楼 liangyong1107 的回复:]
引用 4 楼 fredrickhu 的回复:
SQL code
select
cast((rand(checksum(newid()))*130) as int),
cast((rand(checksum(newid()))*10) as int),
cast((rand(checksum(newid()))*130) as int),0,'001'
from
master..s……
[/Quote]
[Quote=引用 5 楼 liangyong1107 的回复:]
引用 4 楼 fredrickhu 的回复:
SQL code
select
cast((rand(checksum(newid()))*130) as int),
cast((rand(checksum(newid()))*10) as int),
cast((rand(checksum(newid()))*130) as int),0,'001'
from
master..s……
[/Quote]
--小F-- 2011-08-09
- 打赏
- 举报
select
cast((rand(checksum(newid()))*130) as int),
cast((rand(checksum(newid()))*10) as int),
cast((rand(checksum(newid()))*130) as int),0,'001'
from
master..spt_values
where
[type] = 'p' and number <= 130 and number>=0
一缕青烟 2011-08-09
- 打赏
- 举报
declare @i int;
set @i=1;
while(@i<=130)
begin
insert into BlockTable values(cast(RAND()*10 as int),@i,@i,0,'001')
set @i=@i+1;
end
AcHerat 元老 2011-08-09
- 打赏
- 举报
create table blocktable(blockno int,binno int,rack int,chipcount int,machineno varchar(10))
go
insert into blocktable(blockno,binno,rack,chipcount,machineno)
select cast((rand(checksum(newid()))*130) as int),cast((rand(checksum(newid()))*10) as int),
cast((rand(checksum(newid()))*130) as int),0,'001'
from master..spt_values
where [type] = 'p' and number <= 130
select *
from blocktable
drop table blocktable
/**************
blockno binno rack chipcount machineno
----------- ----------- ----------- ----------- ----------
85 9 35 0 001
106 4 127 0 001
69 1 122 0 001
46 7 116 0 001
3 2 95 0 001
42 7 17 0 001
92 1 81 0 001
122 5 86 0 001
34 7 34 0 001
113 9 71 0 001
63 3 119 0 001
125 2 108 0 001
116 8 76 0 001
67 0 10 0 001
128 2 119 0 001
87 3 75 0 001
6 4 15 0 001
105 8 124 0 001
105 5 72 0 001
88 2 11 0 001
91 6 102 0 001
52 9 122 0 001
108 2 119 0 001
108 3 125 0 001
48 8 82 0 001
57 4 76 0 001
8 2 41 0 001
58 4 42 0 001
129 1 19 0 001
12 9 78 0 001
120 5 33 0 001
90 1 106 0 001
64 1 36 0 001
0 2 122 0 001
93 5 62 0 001
87 3 55 0 001
8 6 13 0 001
78 2 17 0 001
9 0 33 0 001
36 6 114 0 001
0 2 89 0 001
73 3 99 0 001
10 6 107 0 001
105 3 109 0 001
75 9 127 0 001
67 2 37 0 001
33 1 8 0 001
94 9 35 0 001
3 1 56 0 001
112 9 109 0 001
126 9 29 0 001
65 9 117 0 001
122 6 93 0 001
22 4 116 0 001
3 4 82 0 001
0 5 27 0 001
37 9 36 0 001
101 9 66 0 001
1 9 112 0 001
42 2 37 0 001
32 5 101 0 001
110 9 71 0 001
89 5 74 0 001
92 4 54 0 001
89 8 87 0 001
45 7 39 0 001
80 6 64 0 001
118 4 85 0 001
4 0 19 0 001
18 4 31 0 001
68 7 46 0 001
38 1 55 0 001
37 5 37 0 001
113 3 32 0 001
10 0 21 0 001
46 0 73 0 001
14 8 121 0 001
35 9 56 0 001
36 1 7 0 001
17 1 22 0 001
92 5 86 0 001
72 9 26 0 001
101 2 8 0 001
63 9 106 0 001
92 7 70 0 001
127 1 97 0 001
97 8 61 0 001
22 9 17 0 001
92 9 106 0 001
117 7 13 0 001
89 1 12 0 001
47 0 98 0 001
94 8 83 0 001
114 7 46 0 001
59 0 13 0 001
53 1 91 0 001
2 1 121 0 001
3 7 39 0 001
24 1 34 0 001
47 2 33 0 001
8 3 85 0 001
67 1 10 0 001
6 6 11 0 001
16 9 21 0 001
74 8 74 0 001
33 3 129 0 001
107 6 118 0 001
60 7 38 0 001
86 4 56 0 001
92 9 44 0 001
128 1 124 0 001
68 4 62 0 001
11 7 75 0 001
35 6 54 0 001
92 8 105 0 001
93 2 111 0 001
73 3 52 0 001
9 7 76 0 001
115 9 18 0 001
41 6 74 0 001
36 8 14 0 001
14 8 89 0 001
104 0 93 0 001
3 8 118 0 001
91 9 26 0 001
56 7 45 0 001
43 8 27 0 001
3 0 37 0 001
65 3 84 0 001
31 6 90 0 001
98 4 24 0 001
(131 行受影响)
AcHerat 元老 2011-08-09
- 打赏
- 举报
insert into BlockTable(BlockNo,BinNo,Rack,ChipCount,MachineNo)
select cast((rand()*130) as int),cast((rand()*10) as int),cast((rand()*130) as int),0,'001'
from master..spt_values
where [type] = 'p' and number <= 130
select cast((rand()*130) as int),cast((rand()*10) as int),cast((rand()*130) as int),0,'001'
from master..spt_values
where [type] = 'p' and number <= 130
