----- 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.
Rhino
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
