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!

On Fri, 15 Oct 2010 14:36:01 -0500, MikeB <mpbrede (AT) gmail (DOT) com> wrote:

Not using the VALUES() syntax, no. You'll need a table.

If they're coming from MySQL, why not just link to the MySQL table and use it
as the source of the SELECT FROM? Or dump the MySQL data into a temp table?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

MikeB wrote:
Why not link to the MySQL table and insert the data directly using
insert...select?

What are those delimiters? Why are you even bothering to delimit these
words?

You're inserting a date/time as text?? Is the destination a Jet table?

, '733997', 'Karsten75', 'Marauders', 8243, 1),
Patient: It hurts when I raise my arm above my head
Doctor: Well don't do that!

Pretty much, although there is a trick. There has to be a table that has
at least a single row of data in it. it doesn't have to match the
structure of the table into which you are inserting. The idea is to
create a union query using that base table, using expressions for each
column to be inserted:

INSERT INTO `scoresUncharted` (`ts`, `map`, `user`, `group`, `score`,
`playCount`)
SELECT TOP 1 #2010-08-14 11:39:14# As ts, '733997' As map, ...
FROM basetable
UNION ALL
SELECT TOP 1 #2010-08-22 09:08:13# , '734370', ...
FROM basetable



--
HTH,
Bob Barrows

John W. Vinson wrote:

Aye, and therein lies the rub, I first have to get the data into a
table... Chicken and egg situation it would seem.


Well, the MySQL table was dumped on another person's system and sent to
me as a file....

All that I have is this single .SQL file.

Mike

Bob Barrows wrote:
Because I only have the dump and not the MySQL table or database system.

That's how it came in the file and since it worked for a single row, I
didn't even bother to try and change anything, since it doesn't seem to
break anything.

I guess. Whatever Access uses as default. I can change it if need be,
but I thought I'd first see what I get.

Yea, funny that.

Pardon my utter ignorance, but may I ask why the "select top 1" appears
in there twice? does that not simply select 1 row and that's it?

On Fri, 15 Oct 2010 15:39:10 -0500, MikeB <mpbrede (AT) gmail (DOT) com> wrote:


What's the format of the MySQL dump? Comma separated text strings? If so you
can use File... Get External Data... Link, and select .txt as Files Of Type.
Or you can Import the text file into a temp table. You might need to rename
the file to use a .txt or .csv extension to get Access to recognize it, but if
you have the data in computer readable form you can import it, one way or
another!

Could you post a (perhaps fake data but correct file format) example of a few
records of the "dump" file?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

John W. Vinson wrote:

THe data I posted above is in the exact format that it is in in the SQL
file.

Here's a bit of the "stuff before the actual data:

-- phpMyAdmin SQL Dump
-- version 2.11.3deb1ubuntu1.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 24, 2010 at 08:24 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.4-2ubuntu5.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `mapCharts`
--

-- --------------------------------------------------------

--
-- Table structure for table `scoresUncharted`
--

CREATE TABLE IF NOT EXISTS `scoresUncharted` (
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP,
`map` varchar(32) NOT NULL,
`user` varchar(15) NOT NULL,
`group` varchar(20) NOT NULL,
`score` mediumint(8) unsigned NOT NULL,
`playCount` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`map`,`user`),
KEY `ts` (`ts`),
KEY `map` (`map`),
KEY `user` (`user`),
KEY `group` (`group`),
KEY `score` (`score`),
KEY `playCount` (`playCount`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `scoresUncharted`
--

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),
('2010-08-14 12:07:57', '730003', 'Karsten75', 'Marauders', 8647, 1),
('2010-08-14 16:26:58', '714566', 'Karsten75', 'Marauders', 9240, 1),
('2010-08-15 00:59:46', '733996', 'Karsten75', 'Marauders', 8139, 1),
('2010-08-15 11:02:53', '0', 'Karsten75', '', 9295, 3),

On Fri, 15 Oct 2010 16:14:29 -0500, MikeB <mpbrede (AT) gmail (DOT) com> wrote:

In that case I'd just (manually or programmatically) discard the header
"stuff", and use File... Get External Data... Link to link to the file; then
run an Append query to migrate it into your Access table. You might need to
use

CDate([ts])

in the query to convert the value to Date/TIme, but it may well work directly.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

MikeB wrote:
Even though it appears that John is steering you in a better direction, I
figured I would answer your question anyways:

In a union query, each select statement is a separate query. The union is
merging the results from those queries into a single resultset. So you need
to force each query to return a single row, instead of a row for each record
in the basetable. If the base table contains only a single row, then the
TOPs are not necessary.

On Oct 15, 2:36*pm, MikeB <mpbr... (AT) gmail (DOT) com> wrote:
You need a ; after each row of values instead of a ,