This procedure inserts rows by using while loop : while « Transact SQL « SQL Server / T-SQL Tutorial






6> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
7>                   project_no CHAR(4) NOT NULL,
8>                   job CHAR (15) NULL,
9>                   enter_date DATETIME NULL)
10> GO
1>
2>
3>       declare @i integer, @j integer
4>       declare @job char(20), @enter_date datetime
5>       declare @project_no char(4)
6>       declare @dept_no char(4)
7>       set @i = 1
8>       set @j = 1
9>       set @job = 'Analyst'
10>       set @enter_date = GETDATE()
11>       set @dept_no = 'd1'
12>       while @i < 3001
13>       begin
14>         while @j < 5
15>          begin
16>           if (@j = 1) set @dept_no = 'd1'
17>           else if (@j = 2) set @dept_no = 'd2'
18>            else if (@j = 3) set @dept_no = 'd3'
19>            else set @dept_no = 'd4'
20>        insert into works_on
21>            values (@i, @dept_no, @job, @enter_date)
22>        set @j = @j+1
23>         end
24>       set @i = @i+1
25>       set @j = 1
26>       end
27> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)








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