SQL help (mysql)

[PaulStat]

I'm trying to insert some data from a temporary table to a permanent one.

Here's what I have

CREATE TABLE GENRE (
GENREID INT NOT NULL AUTO_INCREMENT, GENRENAME VARCHAR(50), PRIMARY KEY(GENREID)
);

INSERT INTO GENRE (GENRENAME) VALUES ("news");

CREATE TEMPORARY TABLE TVTEMPTABLE (
PROGTITLE VARCHAR(50), SUBTITLE VARCHAR(50),
EPISODE VARCHAR(50), YR YEAR, DIRECTOR VARCHAR(50),
PERFORMERS TEXT, PREMIERE BOOL, FILM BOOL, RPEAT BOOL,
SUBTITLES BOOL, WIDESCREEN BOOL, NEWSERIES BOOL, DEAFSIGNED BOOL,
BNW BOOL, STARRATING TINYINT, CERTIFICATE VARCHAR(5), GENRE VARCHAR(20),
DESCRIPTION TEXT, CHOICE BOOL, PROGDATE DATE, STARTIME TIME, ENDTIME TIME, DURATION TINYINT
);

INSERT INTO TVTEMPTABLE (
PROGTITLE, SUBTITLE, EPISODE, YR, DIRECTOR, PERFORMERS,
PREMIERE, FILM, RPEAT, SUBTITLES, WIDESCREEN, NEWSERIES,
DEAFSIGNED, BNW, STARRATING, CERTIFICATE, GENRE, DESCRIPTION, CHOICE, PROGDATE, STARTIME, ENDTIME, DURATION)
VALUES ("bbc breakfast","n/a","n/a","2011","n/a","n/a","0","0","0","0",
"0","0","0","0","5","PG","news","news","0","2011-01-01","10:00:00","10:00:00","90"
);

CREATE TABLE PROGRAMME (
PROGRAMMEID INT NOT NULL AUTO_INCREMENT, GENREID INT NOT NULL, PROGTITLE VARCHAR(50), YR YEAR,
DIRECTOR VARCHAR(50), PERFORMERS TEXT, PREMIERE BOOL, FILM BOOL, RPEAT BOOL,
SUBTITLES BOOL, WIDESCREEN BOOL, NEWSERIES BOOL, DEAFSIGNED BOOL,
BNW BOOL, STARRATING TINYINT, CERTIFICATE VARCHAR(5), DESCRIPTION TEXT, CHOICE BOOL,
PRIMARY KEY(PROGRAMMEID), INDEX (GENREID), FOREIGN KEY (GENREID) REFERENCES GENRE(GENREID)
) ENGINE=INNODB;

INSERT INTO PROGRAMME (
GENREID, PROGTITLE, YR, DIRECTOR, PERFORMERS, PREMIERE, FILM, RPEAT, SUBTITLES,
WIDESCREEN, NEWSERIES, DEAFSIGNED, BNW, STARRATING, CERTIFICATE, DESCRIPTION, CHOICE)
SELECT G.GENREID, T.PROGTITLE, T.YR, T.DIRECTOR, T.PERFORMERS, T.PREMIERE,
T.FILM, T.RPEAT, T.SUBTITLES, T.WIDESCREEN, T.NEWSERIES, T.DEAFSIGNED,
T.BNW, T.STARRATING, T.CERTIFICATE, T.DESCRIPTION, T.CHOICE
FROM TVTEMPTABLE T,GENRE G WHERE G.GENRENAME = T.GENRE AND NOT EXISTS (
SELECT * FROM PROGRAMME P WHERE P.PROGTITLE = T.PROGTITLE );

The insert into the programme table fails with

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Any ideas?

 

[PaulStat]

spotted it, was trying to insert too many columns

 

[fez]

You are telling mysql to insert x number of values into a set of fields which does not tally with x.

I think part of your problem might lie in using double quotes instead of single quotes to wrap values.

you need to have:
"bbc breakfast","n/a","n/a","2011","n/a","n/a","0","0","0","0",
"0","0","0","0","5","PG","news","news","0","2011-01-01","10:00:00","10:00:00","90"

looking like:

'bbc breakfast','n/a','n/a','2011','n/a','n/a','0','0','0','0',
'0','0','0','0','5','PG','news','news','0','2011-01-01','10:00:00','10:00:00','90'