Query Insert Into

[cms9651]

Hi everyone.

I need import in the table _int all values of the table _temp.

I don't modify the table _temp...

I use query insert in mysql:

insert into _int
(select * from _temp)

But I have this problem:

1) [Err] 1136 - Column count doesn't match value count at row 1, because in the table _temp I don't have id field...
2) [Err] 1292 - Incorrect datetime value: '15/05/2010 12.22' for column 'myDATE' at row 1

DROP TABLE IF EXISTS `_temp`; 
CREATE TABLE `_temp` (   
   `SEZ2` varchar(255) default NULL,   
   `myDATE` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

DROP TABLE IF EXISTS `_int`; 
CREATE TABLE `_temp` (  
   `ID` int(10) NOT NULL auto_increment, 
   `SEZ2` varchar(255) default NULL,   
   `myDATE` datetime default NULL,
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Can you help me?

[guido2004]

1) Don't use the *, but specify the columns of the _temp table you want to use.
2) Take a look at http://dev.mysql.com/doc/refman/5.1/...tion_timestamp <-- on second thought this may not work, maybe you'll have to manipulate the value of myDATE before you can use it.

[cms9651]

1) Don't use the *, but specify the columns of the _temp table you want to use.

Thanks... but...

[SQL] insert into _int (select SEZ2 from _temp)

[Err] 1136 - Column count doesn't match value count at row 1

[SQL] insert into _int (select TIMESTAMP(`myDate`) from _temp)

[Err] 1136 - Column count doesn't match value count at row 1

[guido2004]

Try

INSERT INTO _int 

SELECT
    0
  , SEZ2 
  , myDATE
FROM _temp

Of course, this will still give the error on myDATE.

[cms9651]

Try

INSERT INTO _int 

SELECT
    0
  , SEZ2 
  , myDATE
FROM _temp

Of course, this will still give the error on myDATE.

[SQL] insert into _int 

(select 
       0 
     , SEZ2
     , myDATE 
from _temp)

[Err] 1136 - Column count doesn't match value count at row 1

[guido2004]

Where do these ( and ) come from?

[cms9651]

From table _temp to table _int...

DROP TABLE IF EXISTS `_temp`; 
CREATE TABLE `_temp` (   
   `SEZ2` varchar(255) default NULL,   
   `myDATE` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

DROP TABLE IF EXISTS `_int`; 
CREATE TABLE `_int` (  
   `ID` int(10) NOT NULL auto_increment, 
   `SEZ2` varchar(255) default NULL,   
   `myDATE` datetime default NULL,
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[guido2004]

From table _temp to table _int...

Actually they come from you

Try the query without those ( and ) around the select statement, and see if anything changes.

[cms9651]

Actually they come from you

Try the query without those ( and ) around the select statement, and see if anything changes.

insert into _int 
select * from _temp

[Err] 1292 - Incorrect date value: '15/05/2010 12.22' for column 'myDate' at row 1

insert into __int
select `TIMESTAMP`(`myDate`)
from _temp

[Err] 1630 - FUNCTION db.TIMESTAMP does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

[guido2004]

insert into _int 
select * from _temp

[Err] 1292 - Incorrect date value: '15/05/2010 12.22' for column 'myDate' at row 1

Great, one problem solved. Now try to get that date in a valid form

[cms9651]

Great, one problem solved. Now try to get that date in a valid form

[SQL] insert into _int
select `TIMESTAMP`(`myDate`)
from _temp

[Err] 1630 - FUNCTION db.TIMESTAMP does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

[guido2004]

You can't select just one column, you need two (_int actually has 3 columns, but 1 is an autoincrement column, so that one gets its values automatically).
And don't put the function name between backticks.

[cms9651]

You can't select just one column, you need two (_int actually has 3 columns, but 1 is an autoincrement column, so that one gets its values automatically).
And don't put the function name between backticks.

OK:

[SQL] insert into _int
select 
`SEZ2`,
 TIMESTAMP(`myDate`)
from _temp 

[Err] 1292 - Incorrect datetime value: '15/05/2010 12.22'

[guido2004]

OK:

[SQL] insert into _int
select 
`SEZ2`,
 TIMESTAMP(`myDate`)
from _temp 

[Err] 1292 - Incorrect datetime value: '15/05/2010 12.22'

Try to get the date in this format:

'2010-05-15 12:22:00'

MySQL functions: http://dev.mysql.com/doc/refman/5.1/en/functions.html

[cms9651]

Try to get the date in this format:

'2010-05-15 12:22:00'

MySQL functions: http://dev.mysql.com/doc/refman/5.1/en/functions.html

Ok:

[SQL] insert into _int
select 
`SEZ2`,
 DATE_FORMAT(`myDate`,'%Y-%m-%d %H:%i:%s'),
0
from _temp_appoggio_interruzioni 

[Err] 1292 - Incorrect datetime value: '15/05/2010 12.22'

[guido2004]

You can't use any datetime functions on the date while it has the wrong format, I think. Try string functions like CONCAT and SUBSTRING

Of course, the easiest would be to have the date column defined as datetime in _temp as well

[cms9651]

You can't use any datetime functions on the date while it has the wrong format, I think. Try string functions like CONCAT and SUBSTRING

Of course, the easiest would be to have the date column defined as datetime in _temp as well

Great!, thanks!

[SQL] 

insert into _int

select 
0,
`SEZ2`,
str_to_date(`myDate`, '%d/%m/%Y %H.%i')
from _temp