List:General Discussion« Previous MessageNext Message »
From:Rhino Date:September 28 2004 9:41pm
Subject:Re: Multiple Insert Statement?
View as plain text  
----- Original Message ----- 
From: "Eve Atley" <eatley@stripped>
To: "Paul DuBois" <paul@stripped>; <mysql@stripped>;
<SGreen@stripped>
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

Thread
ImagesGH28 Sep
  • Re: ImagesEric Bergen28 Sep
  • Re: ImagesJigal van Hemert28 Sep
    • Re: ImagesDreamWerx28 Sep
      • Re: ImagesGH28 Sep
        • Re: ImagesDreamWerx28 Sep
          • RE: ImagesEd Lazor28 Sep
RE: Imagesjabbott28 Sep
  • RE: ImagesEd Lazor28 Sep
Re: ImagesEric Bergen28 Sep
  • RE: ImagesEd Lazor28 Sep
  • RE: ImagesEd Lazor28 Sep
Re: ImagesDreamWerx28 Sep
  • RE: ImagesEd Lazor28 Sep
  • Multiple Insert Statement?Eve Atley28 Sep
    • Re: Multiple Insert Statement?GH28 Sep
      • Re: Multiple Insert Statement?SGreen28 Sep
    • Re: Multiple Insert Statement?Paul DuBois28 Sep
      • RE: Multiple Insert Statement?Eve Atley28 Sep
        • RE: Multiple Insert Statement?mos28 Sep
        • Re: Multiple Insert Statement?Rhino28 Sep
        • Re: Multiple Insert Statement?Rhino29 Sep
  • RE: ImagesEd Lazor28 Sep
Re: ImagesDreamWerx28 Sep
  • Re: ImagesGH28 Sep
    • Re: ImagesDreamWerx28 Sep
Re: ImagesDreamWerx28 Sep
Re: ImagesGH28 Sep
Re: ImagesDreamWerx28 Sep