List:General Discussion« Previous MessageNext Message »
From:Rhino Date:September 28 2004 10:17pm
Subject:Re: Multiple Insert Statement?
View as plain text  
Sorry, there were a few typos in my reply. I have amended the reply at the
bottom....

Rhino

----- Original Message ----- 
From: "Rhino" <rhino1@stripped>
To: <eatley@stripped>; "Paul DuBois" <paul@stripped>;
<mysql@stripped>; <SGreen@stripped>
Sent: Tuesday, September 28, 2004 5:41 PM
Subject: Re: Multiple Insert Statement?


>
> ----- 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.
>

===========
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

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