SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Query Insert Into

    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:

    Code:
    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

    Code:
    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    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.

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    1) Don't use the *, but specify the columns of the _temp table you want to use.
    Thanks... but...

    Code:
    [SQL] insert into _int (select SEZ2 from _temp)
    
    [Err] 1136 - Column count doesn't match value count at row 1
    Code:
    [SQL] insert into _int (select TIMESTAMP(`myDate`) from _temp)
    
    [Err] 1136 - Column count doesn't match value count at row 1

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Try
    Code:
    INSERT INTO _int 
    
    SELECT
        0
      , SEZ2 
      , myDATE
    FROM _temp
    Of course, this will still give the error on myDATE.

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Try
    Code:
    INSERT INTO _int 
    
    SELECT
        0
      , SEZ2 
      , myDATE
    FROM _temp
    Of course, this will still give the error on myDATE.

    Code:
    [SQL] insert into _int 
    
    (select 
           0 
         , SEZ2
         , myDATE 
    from _temp)
    
    [Err] 1136 - Column count doesn't match value count at row 1

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Where do these ( and ) come from?

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    From table _temp to table _int...


    Code:
    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. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cms9651 View Post
    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.

  9. #9
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Actually they come from you

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


    Code:
    insert into _int 
    select * from _temp
    [Err] 1292 - Incorrect date value: '15/05/2010 12.22' for column 'myDate' at row 1

    Code:
    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. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cms9651 View Post
    Code:
    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

  11. #11
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Great, one problem solved. Now try to get that date in a valid form
    Code:
    [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


  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    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. #13
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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:

    PHP Code:
    [SQLinsert into _int
    select 
    `SEZ2`,
     
    TIMESTAMP(`myDate`)
    from _temp 
    [Err] 1292 - Incorrect datetime value: '15/05/2010 12.22'

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by cms9651 View Post
    OK:

    PHP Code:
    [SQLinsert 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

  15. #15
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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:

    PHP Code:
    [SQLinsert 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. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,408
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    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. #17
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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!

    PHP Code:
    [SQL

    insert into _int

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •