Insert 100 rows of data: RAND : while « Transact SQL « SQL Server / T-SQL Tutorial






5> CREATE TABLE random_data
6> (
7> col1        int PRIMARY KEY,
8> col2        int,
9> col3        char(15)
10> )
11> GO
1>
2> DECLARE @counter int, @col2 int, @col3 char(15)
3> 
4> 
5> SELECT @counter=0, @col2=RAND(@@spid + cpu + physical_io)
6> FROM master..sysprocesses where spid=@@spid
7>
8> WHILE (@counter < 1000)
9>     BEGIN
10>     SELECT @counter=@counter + 10,   
11>     @col2=
12>         CASE        
13>             WHEN CONVERT(int, RAND() * 1000) % 2 = 1
14>             THEN (CONVERT(int, RAND() * 100000) % 10000 * -1)
15>             ELSE CONVERT(int, RAND() * 100000) % 10000
16>         END,
17>     @col3=       
18>         CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) -- 65 is 'A'
19>             + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
20>             + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
21>             + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65)
22>             + REPLICATE(CHAR((CONVERT(int, RAND() * 1000) % 26 )
23>             + 65), 11)
24>
25>     INSERT random_data VALUES (@counter, @col2, @col3)
26>     END
27> GO
1>
2> drop table random_data;
3> GO








20.8.while
20.8.1.How to perform repetitive processing
20.8.2.WHILE @@FETCH_STATUS = 0
20.8.3.This procedure inserts rows by using while loop
20.8.4.Insert 100 rows of data: RAND
20.8.5.While with counter
20.8.6.Use while loop to insert data
20.8.7.While loop controlled by an aggregate function
20.8.8.WHILE with AND operator
20.8.9.A script that tests and adjusts credit amounts with a WHILE loop
20.8.10.Using a Subquery with a Single-Statement WHILE Loop