I need to insert multiple rows at once in sql server ..based on an iterative value

Question

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.

Cowan · Accepted Answer · 2010-05-11 07:13:01Z

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

Preet Sangha · Accepted Answer · 2010-05-11 06:59:44Z

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

answered May 11, 2010 at 6:59

Preet Sangha

65.7k20 gold badges152 silver badges227 bronze badges

1 Comment

Nitin Midha Over a year ago

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: stackoverflow.com/questions/2427333/…

Nitin Midha · Accepted Answer · 2010-05-11 07:00:57Z

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

adopilot · Accepted Answer · 2010-05-11 07:02:10Z

declare @rbr int set @rbr=0 while @rbr<3 begin insert into tblTemp2 select a+@rbr, b from tblTemp2 set @rbr=@rbr+1 end

Collectives™ on Stack Overflow

I need to insert multiple rows at once in sql server ..based on an iterative value

4 Answers 4

Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Linked

Related