For Loop case
- 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:
Second cycle
Third cycle
Fourth cycle
Fifth cycle
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