SQL LOOP INSERT Based on List of ID's

Question

Hey I have SQL writers block. So here is what I'm trying to do based on pseudo-code

int[] ids = SELECT id FROM (table1) WHERE idType = 1 -> Selecting a bunch of record ids to work with
FOR(int i = 0; i <= ids.Count(); ++i) -> loop through based on number of records retrieved
{
    INSERT INTO (table2)[col1,col2,col3] SELECT col1, col2, col3 FROM (table1)
    WHERE col1 = ids[i].Value AND idType = 1 -> Inserting into table based on one of the ids in the array

    // More inserts based on Array ID's here
}

This is sort of the idea I'm trying to achieve, I understand that arrays are not possible in SQL but I've listed it here to explain my goal.

What DBMS are you using? The answer for T-SQL would be different than the answer for mySQL. — smdrager
– smdrager, Commented Apr 5, 2011 at 17:23

Mikael Eriksson · Accepted Answer · 2011-04-05 17:36:27Z

29

This is what you are asking for.

declare @IDList table (ID int)

insert into @IDList
SELECT id
FROM table1
WHERE idType = 1

declare @i int
select @i = min(ID) from @IDList
while @i is not null
begin
  INSERT INTO table2(col1,col2,col3) 
  SELECT col1, col2, col3
  FROM table1
  WHERE col1 = @i AND idType = 1

  select @i = min(ID) from @IDList where ID > @i
end

But if this is all you are going to do in the loop you should really use the answer from Barry instead.

answered Apr 5, 2011 at 17:36

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

codingbadger · Accepted Answer · 2011-04-05 17:26:33Z

11

You can just use:

Insert Into Table2 (Col1, Col2, Col3)
Select col1, Col2, Col3
From Table1
Where idType = 1

Why would you even need to loop through each id individually

answered Apr 5, 2011 at 17:26

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

2 Comments

Ayo Over a year ago

Sorry I didnt want to make my question long and complicated but there will be other inserts based of the id in the loop.

codingbadger Over a year ago

@Ayo: By not including all the information in your question, actually delays you getting the answer you need. Pseudo code is fine as long as the required logic is still there. In any case the answer provided by @Mikael should do want you need.

Dustin Laine · Accepted Answer · 2011-04-05 17:26:06Z

8

INSERT INTO table2
(
    col1,
    col2,
    col3
)
SELECT 
    table1.col1, 
    table1.col2, 
    table1.col3
FROM table1
WHERE table1.ID IN (SELECT ID FROM table1 WHERE table1.idType = 1)

answered Apr 5, 2011 at 17:26

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

3 Comments

Conrad Frix Over a year ago

Why bother with the IN? Why not just WHERE table1.idType = 1

Vinoth Over a year ago

If suppose col2 value has to be fetched from different table (other than table1) how to chnage this query?

curiousBoy Over a year ago

If the Ids are inputted and used as duplicate in IN() statement, it will only calculate each id's value once. For instance: Select SUM (Price) FROM SoldProducts WHERE spid in (1,2,3,4,5,1,3,5) It will output the same result with: Select SUM (Price) FROM SoldProducts WHERE spid in (1,2,3,4,5)

Collectives™ on Stack Overflow

SQL LOOP INSERT Based on List of ID's

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related