SQL for Loop syntax case

For Loop case

1. 1. Create a table with the following data:

CREATE Table A (

ID int,

Name varchar (30)

Id	Name
1	Guan yu
500	Zhang Fei
1000	Zhao Yun
100	Ma Chao
200	Jack Huang

2. Create B table, now requires the table a data loop inserted into the B table, each time the loop insert a piece of data, the results are as follows:

Id	Name
1	Guan yu
500	Zhang Fei
1000	Zhao Yun
100	Ma Chao
200	Jack Huang

3. Because the ID in the table is not contiguous, we first need to number the ID, create a temporary table #c, insert a table data into the #c,

Select Identity (int,0,1) as l,id,name into #c from a

Select * FROM #c--Query temporary table #c

drop table #c – Delete temporary table #c

This is our ID with the corresponding number,

L	Id	Name
1	1	Guan yu
2	500	Zhang Fei
3	1000	Zhao Yun
4	100	Ma Chao
5	200	Jack Huang

4. We finally put the data of the #c table in turn and insert it into table B

DECLARE @a int

Set @a=0

Select Identity (int,0,1) as l,id,name into #c from a

--select * from #c

While @a<10

Begin

--select * from B

Insert into B (id,name) Select Id,name from #c where [email protected]

Set @[email protected]+1

End

SELECT * from b

5. We then loop the data of table a sequentially into table B,

First-time loop insertion:

L	Id	Name
1	1	Guan yu

Second cycle

2

500

Zhang Fei

Third cycle

3

1000

Zhao Yun

Fourth cycle

4

100

Ma Chao

Fifth cycle

5

200

Jack Huang

Sixth cycle

Countless data insertions

Seventh cycle

Countless data insertions

Eighth cycle

Countless data insertions

Nineth Cycle

Countless data insertions

Tenth cycle

Countless data insertions

6. Complete the stored procedure Code:

CREATE PROCEDURE Sp_xh

As

Begin

TRUNCATE TABLE b--emptying the records of table B

DECLARE @a INT--Declaring variable

Set @a=0

Select Identity (int,0,1) as l,id,name into #c from a

--select * from #c

While @a<10-the total number of cycles is less than 10

Begin

--select * from B-check new B table data

Insert into B (id,name) Select Id,name from #c where [email protected]

Set @[email protected]+1

End

End

--exec Sp_xh

SQL for Loop syntax case