I have a table that I want to copy selected fields into another table.
The footprint of the tables is different. Here is the INSERT I am
trying to use.

An error is returned, "SQL Error (1136); Column count doesn't match
value count at row 1.
I've run the SELECT without the INSERT line and it does select the
data I want to copy.

Comparing the fields in "births"with the fields defined by the SELECT,
The only field that is missing is the primary key (birthid), which I
assumed the INSERT would fill in..

USE genealogyvr;

INSERT IGNORE INTO births
SELECT
NewspaperKey AS newspaperid,
CONCAT(Year, "-",
IF(Month < 10, CONCAT("0",Month), Month) ,
"-" ,
IF(Day < 10, CONCAT("0", Day), Day) ) AS paperdate,
Page AS page,
Col AS col,
FatherFirst AS fatherfirst,
FatherMiddle AS fathermiddle,
MotherFirst AS motherfirst,
MotherMiddle AS mothermiddle,
MotherMaiden AS mothermaiden,
First AS childfirst,
Middle AS childmiddle,
Last AS childlast,
DateOfBirth AS dob,
Comments AS comment

FROM birthsaccess
WHERE Year < 1890;

On 2011-03-04 03:47, bruce wrote:
[...]
*Always* specify the column names in the target table, i.e.:

INSERT INTO births
(newspaperid, ...)
SELECT NewspaperKey AS newspaperid, ...

[...]

El 04/03/2011 3:47, bruce escribió/wrote:
Nope, that's not how INSERT works. You just cannot do this:

INSERT INTO foo (id, bar) VALUES ('Hello');



Are you aware that if you ever add new columns to the births table your
application will stop working and if you ever swap columns you
application will start storing invalid data?


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On Mar 4, 4:14*am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH... (AT) demogracia (DOT) com.invalid> wrote:
Basically I solved the problem by deleting the two "extra' columns in
the target table, namely the primary key column and the one data
column I'll be updating later in the application. The data then
loaded. After that I added back the these two columns.

All is good..

Thanks for the response..

Bruce

..oO(bruce)

Bad idea. _Really_ bad.

It's not. You should use a proper SQL statement and adjust that so that
it matches your table structure, not vice versa.

There are reasons why you should always explicitly list the columns you
want to work with. If you ignore this, the next problem will be waiting
just around the corner.

Micha

On Fri, 04 Mar 2011 10:14:32 +0100, ?lvaro G. Vicario wrote:
Right, but just to clarify things for the OP, *if* the id column is one
that's autoincrement on MySQL, IDENTITY on DB2, and other things as
appropriate for other systems, *omitting* the listing of the id column
will do what he's hoping for: assign an appropriate value under the
covers automatically.

INSERT INTO foo (bar) VALUES ('Hello');

could easily result in a row like

id bar
----------- -----------------
1 Hello

--
40. I will be neither chivalrous nor sporting. If I have an unstoppable
superweapon, I will use it as early and as often as possible instead
of keeping it in reserve.
--Peter Anspach's list of things to do as an Evil Overlord