0

I want to insert 3 rows at a time in a table based on select statement..

consider the query

insert into tblTemp 
(
a,b
)

select a, b from tblTemp2

This lets me insert one row in tblTemp..

my requirement is to add 3 rows with iterative values a,a+1,a+2 for each b inserted.

gbn
  • 408,740
  • 77
  • 567
  • 659
sajad
  • 873
  • 2
  • 8
  • 24

4 Answers4

4

Use a cross join to generate extra rows (via Cartesian product), rather than querying tblTemp2 three times with UNION ALL

insert into tblTemp (a, b)
select a + offset, b
from
  tblTemp2
  cross join
  (SELECT 0 AS offset UNION ALL SELECT 1 UNION ALL SELECT 2) foo
Cowan
  • 36,447
  • 11
  • 65
  • 65
gbn
  • 408,740
  • 77
  • 567
  • 659
1
insert into tblTemp ( a,b )
select a, b from tblTemp2 UNION ALL
select a+1, b from tblTemp2 UNION ALL
select a+2, b from tblTemp2 UNION ALL
Preet Sangha
  • 62,844
  • 17
  • 138
  • 209
  • This query is not very efficient as you have to traverse table 3 times, check my response or gbn response. Next two rows are just calculations over the base row. Check this question, it uses the same approach: http://stackoverflow.com/questions/2427333/sql-to-get-list-of-dates-as-well-as-days-before-and-after-without-duplicates/2427836#2427836 – Nitin Midha May 11 '10 at 07:09
1
Insert Into tblTemp (a,b)
SELECT T.a + T1.Num , T.B
FROM 
tblTemp2 T
CROSS JOIN
(
 SELECT 0 as Num
 UNION ALL
 SELECT 1 as Num
 UNION ALL
 SELECT 2 as Num 
) T1
Nitin Midha
  • 2,258
  • 20
  • 22
0
declare @rbr int
set @rbr=0

while @rbr<3
begin
insert into tblTemp2
select a+@rbr, b from tblTemp2
set @rbr=@rbr+1
end
adopilot
  • 4,160
  • 12
  • 63
  • 92