Query Insert Into

Databases
1

Hi everyone.

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

I don’t modify the table _temp… :frowning:

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?

2
  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/en/date-and-time-functions.html#function_timestamp <– on second thought this may not work, maybe you’ll have to manipulate the value of myDATE before you can use it.
3

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
4

Try


INSERT INTO _int 

SELECT
    0
  , SEZ2 
  , myDATE
FROM _temp

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

5 

[SQL] insert into _int 

(select 
       0 
     , SEZ2
     , myDATE 
from _temp)

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

Where do these ( and ) come from?

7

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;
8

Actually they come from you :slight_smile:

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

9 
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

10

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

11 

[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

:shifty:

12

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.

13

OK:

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

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

14

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

15

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’

16

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

17

Great!, thanks! :wink:

[SQL] 

insert into _int

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