Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Question Unanswered: INSERT INTO ... SELECT problem

    Here is my problem:

    I am modifying old DB, which have some fields VARCHAR(3) which contains numbers, for e.g. '234', '456', etc. In the new DB i made it INT field.

    But when I try to copy the data from the old DB using INSERT INTO ... SELECT statement, I receive error, because the different field type... How to fix this ?

  2. #2
    Join Date
    Aug 2004
    Posts
    28

    Hello Dear Member

    Hello Dear Member

    It's working my dear, might be there is some other problem, if you can give the full detail , means which version of sql you are using , is there any constraints in the table with other which can be impact on other table and or any other such information.

    Regards
    Praveen Kumar Pandey

    CoVisible Solutions [I] Pvt. Ltd. - Knowledge Management

  3. #3
    Join Date
    Jan 2004
    Posts
    30
    I am using SQL Server 8.00

    The error message is:
    Server: Msg 213, Level 16, State 4, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    The column count in both table is 10, just the type is different - VARCHAR(3) and INT...

  4. #4
    Join Date
    Jan 2004
    Posts
    30
    Forgot to mention, there are just one INDEX on each table...

  5. #5
    Join Date
    Dec 2003
    Posts
    24
    Use cast or convert function to convert the datatype in u r select statement... It will Work...

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by The-Saint
    The error message is:
    Server: Msg 213, Level 16, State 4, Line 1
    Insert Error: Column name or number of supplied values does not match table definition.

    More likely the col order is different....AND I bet you have something like

    INSERT INTO myTable99 SELECT *....

    You should always supply a column list in both the INSERT INTO and the SELECT...

    You will find that in the long run, your life will be WAY easier....

    You may also have data type conversion issues, but that's not what you have now...

    Post the actual code so we can see it....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2004
    Posts
    30
    it worked with CAST.

    Thanks guys.

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Error #213 cannot be fixed with CAST! Are you sure you told us everything?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Jul 2004
    Posts
    52
    I suspect the select statement was missing a comma causing one column to be an alias for another and while adding CAST he accidently fixed it.

  10. #10
    Join Date
    Jan 2004
    Posts
    30
    The problem was in CAST first, and there was one more column in the new table, so I added one more NULL in the end and it was OK.

    Now it works

Posting Permissions

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