Insert multiple records without loop 1

[vmon]

I have a tblA.Id, tblA.Tot and I want to insert records into tblB without using a FOR or WHILE and fetch. Can this be done?

The number of inserted records for each Id should equal the tblA.Tot. For example.
tblA
Id Tot
1 2
2 3

tblB
Id
1
1
2
2
2

Thanks,
vmon

 

[SmileyConspiracy]

You can do this with a cross join to another table.

declare @tblA table( id int, tot int)
declare @tblB table( id int)

declare @tot table (tot int)

insert @tblA values( 1, 2)
insert @tblA values( 2, 3)

insert @tot values( 1)
insert @tot values( 2)
insert @tot values( 3)

insert @tblB
select a.id
from @tblA a
cross join @tot t
where a.tot >=t.tot

select * from @tblB

Hope this helps,
Stephen.

 

[SQLSister]

Wow, learn something new every day!

Questions about posting. See faq183-874