Insert Multiple Identical Rows 1

[d2g]

Is it possible to insert multiple identical rows using a single query without using a loop to build this query.

for example:

sqlselected = 0
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN="& websitedsn
oConn.CursorLocation = 3
strSQL = "INSERT INTO testtable (testvar) VALUES ('testinsert') "
oConn.Execute strSQL, sqlselected

I would like to create say 500 of the above rows but not use a loop to build the insert query, and not use a loop to do oRs.addnew or anything like that.. Just a simple 1 line query to add a specific amount of rows. I would eventually like to use a script variable to control how many rows to add.

thanks,

D2G

 

[r937]

create an integers table as follows --

create table integers (i integer);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

now you can create as many rows as you wish with a cross join

the following will insert the same string into 500 new rows --

INSERT INTO testtable (testvar) 
select 'testinsert'
  from integers u, integers t, integers h
 where h.i*100+t.i*10+u.i < 500

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[d2g]

Could you break that down for me and explain whats going on? Trying to follow

Thanks!

 

[r937]

it's an INSERT statement which inserts the value 'testinsert' as many times as the SELECT returns rows, which is 500

the SELECT is a cross join of the integers table with itself 3 times, thus generating all possible combinations of 3 integers, of which there are 1000 combinations, but the WHERE clause restricts the results only to those combinations which evaluate to between 0 and 499 (there are 500 of these)

you can see how the cross join works if you run the following query which has extra columns for demo purposes --

select h.i as hundreds
     , t.i as tens
     , u.i as units
     , h.i*100+t.i*10+u.i as expression
  from integers u, integers t, integers h
 where h.i*100+t.i*10+u.i < 500
order by h.i, t.i, u.i

note you wanted to insert the value 'testinsert' but you could also use the expression to actuall number them

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[d2g]

Wow! Thanks! I got it all figured out now.. thats the solution i was looking for! THANKS AGAIN!

D2G