Multiple updates per field as an insert sql 1

[credo]

Hello,
Can someone point me in the right direction to do the following as an insert query:

I have a table that I want to insert multiple rows for each existing item. Currently around 1000 items.

For each item I want to insert 21 new rows
and update a column (in example below Price).

eg the structure would end up looking like

ItemID Price
1 1
1 2
1 3
1 4
.... for 22 rows
1 22

2 1
2 2
2 3
..... etc
2 22

all the way to
1000 1
1000 2
1000 3
.....
1000 22

At the moment my table structure consists of one row per item eg.

ItemID Price
1 0
2 0
3 0
4 0
5 0
...
1000 0

thanks in advance

 

[JoeMiller]

I have a sample db that I made that shows you how you can do this. The query resulting can be used to make a new table with the appropriate number of records. If you provide your email I'll send it along to you.

Joe Miller
joe.miller@flotech.net

 

[Kalin]

Abusing Access you could use the famous Carthesian product.

Create a table with the prices (or calculation of)

AND Create a query based on both tables.

SELECT [columnnames from both tables] FROM [original], [prices]

If you ommit the Where clause all rows in the first table is combined with all rows in the second.

BTW you could also use Do...Loop code.

Grtz,

Kalin

 

[Dzidze]

In your exemple, your prices seem always to be the same (1-21). Since an item can have multiple prices, and prices can be linked to multiple items, then you have a many-to many relationship. To uptimize your database, I would strongly suggest that you create one table for your items, then one for your prices. In between, you create a table that has both primary keys and insert all your items-price data there.

Hope this helps.

Have fun...