Insert multiple rows into table from code

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I'm creating a script file and I need to populate my lookup tables.

How would I write code to populate the values 1, 2 , ....9 (Only an example
so no better ways please)

At the moment I can only populate one value by

INSERT INTO [tbLNumbers]
(Numbers)
VALUES
('1');

I would like something like;

INSERT INTO [tbLNumbers]
(Numbers)
VALUES
('1')
('2')
('3')
('4'); etc

Please help.
 
Chris said:
I'm creating a script file and I need to populate my lookup tables.

How would I write code to populate the values 1, 2 , ....9 (Only an
example
so no better ways please)

At the moment I can only populate one value by

INSERT INTO [tbLNumbers]
(Numbers)
VALUES
('1');

I would like something like;

INSERT INTO [tbLNumbers]
(Numbers)
VALUES
('1')
('2')
('3')
('4'); etc

Please help.
Click to expand...

I don't believe there is any way to do this. You either get your script to
run a loop and carry out multiple insert queries, or more efficiently, you
would pre-populate a table in a separate database and then you could insert
all the records from one table into another with something like:
INSERT INTO tblNum1 ( ANumber )
SELECT BNumber FROM tblNum2
Alternatively, you could not bother writing SQL statements to update, but
use either a DAO or ADO recordset object to do the updating for you.
 
Justin said:
I'm creating a script file and I need to populate my lookup tables.

How would I write code to populate the values 1, 2 , ....9 (Only an
example
so no better ways please)

At the moment I can only populate one value by

INSERT INTO [tbLNumbers]
(Numbers)
VALUES
('1');

I would like something like;

INSERT INTO [tbLNumbers]
(Numbers)
VALUES
('1')
('2')
('3')
('4'); etc

Please help.
Click to expand...

I don't believe there is any way to do this. You either get your script to
run a loop and carry out multiple insert queries, or more efficiently, you
would pre-populate a table in a separate database and then you could insert
all the records from one table into another with something like:
INSERT INTO tblNum1 ( ANumber )
SELECT BNumber FROM tblNum2
Alternatively, you could not bother writing SQL statements to update, but
use either a DAO or ADO recordset object to do the updating for you.
Click to expand...

I agree. I have needed to do something like this at times, and I did it
by creating an "Autonumber" field with ascending (rather than random)
values and just kept pasting records until I has as many as I wanted. I
then changed the type of the "Autonumber" field to "Number". All this
is pretty easy to do.

Something else that might work even better, though it involves a bit
more work, is to use a spreadsheet program like Excel to calculate the
values that you want to have appear in that field -- with Excel formulas
you get lots of flexibility, and you can set up several fields at once
that way. At the top of the Excel table include a row that lists the
column names, and it's easiest if these names contain no spaces (you can
use _underlines_ instead). Then import that Excel table into a new
Access Table; having done so, you can delete the Excel file.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top