SQL LOOP INSERT Based on List of ID's

40,083

Solution 1

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.

Solution 2

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

Solution 3

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)
Share:
40,083

Related videos on Youtube

Ayo
Author by

Ayo

Yo ask

Updated on July 09, 2022

Comments

  • Ayo
    Ayo over 1 year

    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.

  • Conrad Frix
    Conrad Frix over 12 years
    Why bother with the IN? Why not just WHERE table1.idType = 1
  • Ayo
    Ayo over 12 years
    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
    codingbadger over 12 years
    @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.
  • Vinoth
    Vinoth over 9 years
    If suppose col2 value has to be fetched from different table (other than table1) how to chnage this query?
  • curiousBoy
    curiousBoy over 7 years
    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)

Related