How to insert the same record multiple times in a MS Access 2000 database

sobelhaj

Hi all, I'm relatively new to sql and need help to accomplish what should be a straight forward task. I need to insert record templates in a database table. The records are identical except for IDfield which is a sequence from n to n+count. I can't use Autoincrement because I want to be able to enter different templates where I can define the base index n. I'm currently using transactions to insert records in batches but need a faster method using a single SQL statement. Example: 2000 Apple Fruit Granny Smith 2001 Apple Fruit Granny Smith 2002 Apple Fruit Granny Smith .. .. 2900 Apple Fruit Granny Smith A different fruit may have a base index of 6000 and so on. I would appreciate any help you can give me. Thanks.

RobCollins

Not sure if MS Access supports while statement but you can try something like this... it works in SQL Server

declare @counter int
declare @max int
SET @counter = 0
SET @max = 12

while @counter < @max
begin
INSERT INTO tableName (FruitCodeName) VALUES(CONVERT(nvarchar(3), @counter) + 'Apple Fruit Granny Smith' )
SET @counter = @counter + 1
end

PIEBALDconsult

You Can't Do That With One Statement I Do Wonder What Exactly Your Code Is. You Should Be Using A Parameterized Statement, In Which Case You Need Only Set The One Parameter Value And Execute The Statement Again.

sobelhaj

Thanks for your input. I figured how to use AddWithValue but for such a trivial task I was hoping to avoid a loop in my application and let the database engine do the work for me. Thanks

PIEBALDconsult

sobelhaj wrote:

let the database engine do the work for me

That ain't gonna happen.

Mycroft Holmes

sobelhaj wrote:

I can't use Autoincrement because I want to be able to enter different templates where I can define the base index n.

Well technically his design is wrong, the ID field should be stupid bejond identifying the record. What you are calling an ID field is actually your CODe field and may be edited by the users. What happens if they want to change the code for Granny Smith Apples to 7 series? You could always write a scrip/code for the loop passing in the start series and the product. You cannot avoid a loop somewhere.

Never underestimate the power of human stupidity RAH

jschell

sobelhaj wrote:

I would appreciate any help you can give me.

Based on your description - a redesign would probably be the best thing. Why doesn't your record just have the following for where the last value is the count? Apple Fruit Granny Smith 2900