Loop in SQL query

Question

Basically I want to extract id from a table and insert that id in another table with some other data. So the algorythm goes:

foreach id in (select id from table)
{
    insert into table2 values (id,etc,etc);
}

How to perform this function with an SQL query?

It is always better to use non-looping SQL. For DB SQL loops are much harder to optimize and analize then SQL select, insert, update, delete statements. The performance difference is very significant. — Eugen
– Eugen, Commented Nov 18, 2011 at 0:03

soulcheck · Accepted Answer · 2011-11-17 23:36:14Z

6

Use

insert into table2 
select id, etc, etc from table

instead of for loop.

Here's a helpful article.

answered Nov 17, 2011 at 23:36

soulcheck

36.9k6 gold badges95 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

faraz ali Over a year ago

no i just need one value from table i dont need to copy all the table in to the other . i want the ID from table1 and insert it into table2 with some other values for table2 that will remain same in each loop. so only the parameter ID changes after each loop.

GregL Over a year ago

@farazali You can use static/hardcoded values where soulcheck has the "etc, etc". e.g. insert into table2 select id, 'my string', '2011-11-12', 5.0, 4 and so on

Rémi · Accepted Answer · 2011-11-17 23:36:35Z

3

insert into table2 select id,etc,etc from table;

answered Nov 17, 2011 at 23:36

Rémi

3,7611 gold badge31 silver badges43 bronze badges

2 Comments

faraz ali Over a year ago

no i just need one value from table i dont need to copy all the table in to the other . i want the ID from table1 and insert it into table2 with some other values for table2 that will remain same in each loop. so only the parameter ID changes after each loop

Rémi Over a year ago

I don't understand what is wrong. the "etc,etc" part can be anything, and does not come necessarily from table. For instance, you could do: insert into table2 select id,1,2,3 from table

r0ast3d · Accepted Answer · 2011-11-17 23:36:54Z

0

The syntax to use is a SQL statement with a select and insert into combination.

Select Into

http://www.w3schools.com/sql/sql_select_into.asp

answered Nov 17, 2011 at 23:36

r0ast3d

2,6351 gold badge14 silver badges18 bronze badges

Comments

Yogesh Sharma · Accepted Answer · 2017-08-22 07:31:17Z

0

In SQL Query if you want to use looping then use cursor Pls. check below sample

--DECLARE CURSOR
DECLARE @ID VARCHAR(MAX)

  --READ DATA FROM TABLE TO CURSOR

DECLARE IDCR CURSOR FOR SELECT  id FROM table

OPEN IDCR;

FETCH NEXT FROM IDCR INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO table2  VALUES (@ID,etc,etc)

FETCH NEXT FROM IDCR INTO @ID
END
CLOSE IDCR;

DEALLOCATE IDCR;

Please check below ref. link https://learn.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact-sql

answered Aug 22, 2017 at 7:31

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Loop in SQL query

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related