Creating 1 Million Row on the fly

Question

Post reply

Creating 1 Million Row on the fly

karthik M

One Orange Chip

Points: 29139
More actions
May 5, 2008 at 1:31 am

#71251

Dear Experts,
I want to create 1 Million row on the fly without using Cursor or While loop. How ?
Inputs are welcome !
karthik

Viewing 7 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Cross join a couple of system tables together. Jeff usually uses spt_values (in master)

You could use a user table also. All you need is one table of 1000 rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 2

I don't want to use any tables ( including spt_values or Tally table). How to acheive it ?

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

On 2005, you could use a recursive CTE.

Why can't you use a table?

If you absolutely want to do this the hard way, something kinda like this will probably work(partial query only)

SELECT * FROM

(SELECT 1 AS ID

UNION ALL

SELECT 2 AS ID

UNION ALL

... Up to 10... ) virtualTable1

CROSS JOIN

(SELECT 1 AS ID

UNION ALL

SELECT 2 AS ID

UNION ALL

... Up to 10... ) virtualTable2

CROSS JOIN

... Repeat a couple more times until you have enough rows

I doubt this will be particuarly fast, and it will likely impact TempDB badly.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 4

I am using sql2000.

Can you give me the full query ?

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

karthikeyan (5/5/2008)
Can you give me the full query ?

You should be able to figure it out.

I don't have time to type out 6 subqueries each with 10 unioned queries in it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

May 5, 2008 at 3:34 am

#810568

Ok.I will try it out.

karthik