Use while loop to insert data : while « Transact SQL « SQL Server / T-SQL Tutorial






6> DECLARE @T TABLE
7> (
8>   col1 INT NOT NULL PRIMARY KEY,
9>   col2 INT NOT NULL,
10>   filler CHAR(200) NOT NULL DEFAULT('a'),
11>   UNIQUE(col2, col1)
12> );
13> INSERT INTO @T(col1, col2)
14>   SELECT n, (n - 1) % 100 + 1 FROM dbo.Nums
15>   WHERE n <= 100;
16>
17> SELECT * FROM @T WHERE col1 = 1;
18>
19> SELECT * FROM @T WHERE col1 <= 50;
20>
21> SELECT * FROM @T WHERE col2 = 1;
22>
23> SELECT * FROM @T WHERE col2 <= 2;
24>
25> SELECT * FROM @T WHERE col2 <= 50;
26> GO

(100 rows affected)
col1        col2        filler

----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
          1           1 a


(1 rows affected)
col1        col2        filler

----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
          1           1 a

          2           2 a

          3           3 a

          4           4 a

          5           5 a

          6           6 a

          7           7 a

          8           8 a

          9           9 a

         10          10 a

         11          11 a

         12          12 a

         13          13 a

         14          14 a

         15          15 a

         16          16 a

         17          17 a

         18          18 a

         19          19 a

         20          20 a

         21          21 a

         22          22 a

         23          23 a

         24          24 a

         25          25 a

         26          26 a

         27          27 a

         28          28 a

         29          29 a

         30          30 a

         31          31 a

         32          32 a

         33          33 a

         34          34 a

         35          35 a

         36          36 a

         37          37 a

         38          38 a

         39          39 a

         40          40 a

         41          41 a

         42          42 a

         43          43 a

         44          44 a

         45          45 a

         46          46 a

         47          47 a

         48          48 a

         49          49 a

         50          50 a


(50 rows affected)
col1        col2        filler

----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
          1           1 a


(1 rows affected)
col1        col2        filler

----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
          1           1 a

          2           2 a


(2 rows affected)
col1        col2        filler

----------- ----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
          1           1 a

          2           2 a

          3           3 a

          4           4 a

          5           5 a

          6           6 a

          7           7 a

          8           8 a

          9           9 a

         10          10 a

         11          11 a

         12          12 a

         13          13 a

         14          14 a

         15          15 a

         16          16 a

         17          17 a

         18          18 a

         19          19 a

         20          20 a

         21          21 a

         22          22 a

         23          23 a

         24          24 a

         25          25 a

         26          26 a

         27          27 a

         28          28 a

         29          29 a

         30          30 a

         31          31 a

         32          32 a

         33          33 a

         34          34 a

         35          35 a

         36          36 a

         37          37 a

         38          38 a

         39          39 a

         40          40 a

         41          41 a

         42          42 a

         43          43 a

         44          44 a

         45          45 a

         46          46 a

         47          47 a

         48          48 a

         49          49 a

         50          50 a


(50 rows affected)
1>








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