Sorry, there were a few typos in my reply. I have amended the reply at the
bottom....
Rhino
----- Original Message -----
From: "Rhino" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; "Paul DuBois" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, September 28, 2004 5:41 PM
Subject: Re: Multiple Insert Statement?
>
> ----- Original Message -----
> From: "Eve Atley" <[EMAIL PROTECTED]>
> To: "Paul DuBois" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Tuesday, September 28, 2004 3:56 PM
> Subject: RE: Multiple Insert Statement?
>
>
> >
> > Then I need help getting on the right track here. What I really want to
do
> > is something like the following:
> >
> > INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value)
> SELECT
> > * FROM wow.resume r WHERE r.Candidate_ID = '13103';
> >
> > INSERT INTO wow.candidate_erp
> > (Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
> > Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
> SSN,
> > CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
> > Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
> > Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
> > Interview_Availability, Interview_Contact, US_Experience,
> Location_Country)
> > SELECT *
> > FROM wow.candidate c
> > WHERE c.Candidate_ID = '13103';
> >
> > Yet pulled from the resultset in this query:
> >
> > SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
=
> > '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
> > c.Candidate_ID = r.Candidate_ID;
> >
> > Perhaps the above isn't set up correctly, as when I attempt these
queries:
> >
> > INSERT INTO wow.candidate_erp
> > (Candidate_ID, Section_ID, Section_Value)
> > SELECT SQL_CALC_FOUND_ROWS *
> > FROM wow.candidate;
> >
> > INSERT INTO wow.resume_erp
> > (Candidate_ID, Vendor_ID, Last_Name, First_Name, Middle_Initial,
> > Condition_Type, Employer, Country_ID, Visa_Status, Dt_Visa, MMDD_Birth,
> SSN,
> > CSG_Comments, Working, Available, Start_Date, Location, HoldOnPeriod,
> > Relocation, Tech_Ranking, Comm_Ranking, Availability, Cert_Comments,
> > Dt_Submitted, Def_Rate, Def_Rate_Unit, Other_Country, Currency_id,
> > Interview_Availability, Interview_Contact, US_Experience,
> Location_Country)
> > SELECT SQL_CALC_FOUND_ROWS *
> > FROM wow.resume;
> >
> > ...it returns an error of 1136: Column count doesn't match value count
at
> > row 1. The query "INSERT INTO wow.resume_erp (Candidate_ID, Section_ID,
> > Section_Value) SELECT * FROM wow.resume r WHERE r.Candidate_ID =
'13103';"
> > is impractical when my results are over 400.
> >
> The following simple script illustrates that multiple rows can be copied
> from one table into another by putting a Select from the source table
within
> the Insert for the target table.
>
> ----------------------------------------
> #use tmp;
>
> #Drop/Create source table
> drop table if exists source;
> create table if not exists source
> (idno smallint not null,
> surname char(10) not null,
> primary key(id));
>
> #Populate source table
> insert into source (idno, surname) values
> (1, 'Adams'),
> (2, 'Bailey'),
> (3, 'Collins');
>
> #Display populated source table
> select * from source;
>
> #Drop/Create target table
> drop table if exists target;
> create table if not exists target
> (id smallint not null,
> name char(10) not null,
> primary key(id));
>
> #Populate target table
> insert into target (id, name)
> select * from source;
>
> #Display populated target table
> select * from target;
>
> ----------------------------------------
>
> The Insert/Select (second last statement in the script) will work as long
as
> the column list, which is '(id, name)' in this case, has the same number
of
> columns as is returned by the Select clause. In this case, the source
table
> has two columns so 'select *' returns two columns so we have satisfied
this
> requirement.
>
> Also, the two columns identified in the column list must correspond in
> datatype and size to the columns listed in the select. In this case,
'select
> * from source' translates into 'select idno, surname from source'; idno is
a
> smallint as is the corresponding column in the target table, id; surname
is
> a char(10) as is the corresponding column in the target table, name.
> Therefore, the Insert/Select works.
>
> The Insert/Select could also have been written 'insert into target(id,
name)
> select idno, surname from source' and still worked.
>
> However, this would not have worked:
>
> insert into target(id, name) select surname, idno from source;
>
> because the column names don't correspond in datatype and length: id does
> not correspond to surname and name does not correspond to idno.
>
> I hope this clarifies the use of Insert/Select for you.
>
===========
AMENDED REPLY
===========
Most of what I said above is correct but the script had a mistake. (I
started editing the script on the fly to improve it but wasn't able to test
the amended version due to a temporary glitch on our server. I sent the note
anyway, assuming it was correct, and only discovered the error after the
server started working again.)
Here is the amended script; the error was in the primary key definition of
the 'source' table. I also added both of the alternate Insert/Select
statements; the '3rd Attempt' fails because the columns don't correspond.
--------------------------------
select 'Drop/Create source';
drop table if exists source;
create table if not exists source
(idno smallint not null,
surname char(10) not null,
primary key(idno));
select 'Populate source';
insert into source (idno, surname) values
(1, 'Adams'),
(2, 'Bailey'),
(3, 'Collins');
select 'Display source';
select * from source;
select 'Drop/Create target';
drop table if exists target;
create table if not exists target
(id smallint not null,
name char(10) not null,
primary key(id));
select 'Populate target - 1st Attempt';
delete from target;
insert into target (id, name)
select * from source;
select 'Display target - 1st Attempt';
select * from target;
select 'Populate target - 2nd Attempt';
delete from target;
insert into target (id, name)
select idno, surname from source;
select 'Display target - 2nd Attempt';
select * from target;
select 'Populate target - 3rd Attempt';
delete from target;
insert into target (id, name)
select surname, idno from source;
select 'Display target - 3rd Attempt';
select * from target;
--------------------------------
Again, sorry for any confusion.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
