sql loop with insert

Question

sql loop with insert

wdmm

Old Hand

Points: 311
More actions
April 28, 2016 at 9:53 am

#310201

I've placed data into a temp table as such:
declare @i as int
declare @nmbrRow as int
declare @dm table(dM varchar(max))
insert @dm (select lname from names etc..)
set @nmbrRow = (select count(*) from @dm ) -- returns 2 which is the exact row count in the temp table
set @i = 0
select dM from @dm --will return the below table:
dM
----
lastName1
lastName2
What I need to figure out is how to loop through the temp table (@dM) and insert each row element into another table. I thought this would work but it clearly fails.
while @i < @nmbrRow
begin
insert into lastNames (param1,L,S,lname)
values (@param1, '0', '0', (select dM from @dm))
end
How should I go about inserting each row from the @dm temp table into the insert command?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

wdmm (4/28/2016)
I've placed data into a temp table as such:
declare @i as int
declare @nmbrRow as int
declare @dm table(dM varchar(max))
insert @dm (select lname from names etc..)
set @nmbrRow = (select count(*) from @dm ) -- returns 2 which is the exact row count in the temp table
set @i = 0
select dM from @dm --will return the below table:
dM
----
lastName1
lastName2
What I need to figure out is how to loop through the temp table (@dM) and insert each row element into another table. I thought this would work but it clearly fails.
while @i < @nmbrRow
begin
insert into lastNames (param1,L,S,lname)
values (@param1, '0', '0', (select dM from @dm))
end
How should I go about inserting each row from the @dm temp table into the insert command?

This?

insert into lastNames (param1,L,lS,lname)

select @param1,'0','0',dM from @dm;

wdmm Old Hand Points: 311 More actions · Answer 2

Thanks, Lynn - not sure how I over thought that....