How to insert multiple rows into temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to insert multiple rows into temp table

Author

Topic

hosir
Starting Member

1 Post

Posted - 2010-07-03 : 06:59:33

I have the following table.

seq quantity
1 1
2 3
3 4

I want to insert multiple rows into temp table such that the temp table contains

seq
1
2
2
2
3
3
3
3

Do you know how to use one sql statement, instead of cursor looping to do that?

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-03 : 09:04:48

[code]
declare @sample table
(
seq int,
quantity int
)

insert into @sample
select 1, 1 union all
select 2, 3 union all
select 3, 4

-- Using master..spt_values / number table
select s.seq
from @sample s
inner join master..spt_values v on v.type = 'P'
and v.number >= 1
and v.number <= s.quantity
order by s.seq

-- Using number table function
select s.seq
from @sample s
cross apply dbo.F_TABLE_NUMBER_RANGE(1, s.quantity)
order by s.seq

-- using recursive cte
; with rcte
as
(
select seq, quantity
from @sample

union all

select seq, quantity = quantity - 1
from rcte
where quantity > 1
)
select seq
from rcte
order by seq
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources