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)
Related videos on Youtube
Comments
-
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 over 12 yearsWhy bother with the
IN
? Why not justWHERE table1.idType = 1
-
Ayo over 12 yearsSorry 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 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 over 9 yearsIf suppose col2 value has to be fetched from different table (other than table1) how to chnage this query?
-
curiousBoy over 7 yearsIf 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)