Inserting multiple rows via SQL query

#11

Karl wrote:

Quote:

On Oct 15, 2:36 pm, MikeB<mpbr... (AT) gmail (DOT) com> wrote:
Hi, I hope at least one of these newsgroups are still active.

I'm trying to construct am MS Access (2007) query to insert multiple
rows in a table. Actually, I have the data from a dump of a MySQL database.

I'm trying the following syntax, but it fails for more than one row.

INSERT INTO `scoresUncharted` (`ts`, `map`, `user`, `group`, `score`,
`playCount`) VALUES
('2010-08-14 11:39:14', '733997', 'Karsten75', 'Marauders', 8243, 1),
('2010-08-22 09:08:13', '734370', 'Sauffaus3', 'Resubmitted', 7619, 1),

It works for a single row, but not for multiple rows.

So I read up on the syntax and it seems the only way I can insert
multiple rows into an Access table is if the data is already in another
table?

Surely I'm mistaken and there must be a way to insert multiple rows of
data as an SQL query without having to code the

INSERT INTO `scoresUncharted` (`ts`, `map`, `user`, `group`, `score`,
`playCount`) VALUES

part of the query for each and every row?

Thanks!

You need a ; after each row of values instead of a ,

As all others here pointed out, that is not the case. But thanks for
your input.

#12

Hi Mike,

I use MySQL frequently, and move data between it and Access BE files
on a regular basis. If I were in your shoes I would simply install a
local copy of MySQL, even if it is on an old clunker, and then
'restore' the dump file to it. You could then link the tables to
Access and either copy the data across or leave it linked. If you do a
'SELECT INTO' approach using the MySQL linked table(s) as the source
you can import extremely quickly. Setting up a simple MySQL Server
instance is pretty straight forward, and the community version is free
(and all you need). Dont forget to download the ODBC driver too and
install that on your machine with Access that will be doing the
linking. In a heavy data manipulation case you can use Extract
Transform and Load tools (ETL) to push and pull the data around, and
for this I like to use the free Pentaho Data Integration (PDI). You
could in theory read the MySQL dump file directly in PDI and push it
to an Access back end.

Just my 2 censt

The Frog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Inserting multiple rows via SQL query

comp.databases.ms-access comp.databases.ms-access

Re: Inserting multiple rows via SQL query - 10-16-2010 , 03:27 PM

Re: Inserting multiple rows via SQL query - 10-18-2010 , 01:22 AM