none
Strange occurence of error: Insert Error: Column name or number of supplied values does not match table definition.

    Question

  • I think i found an interesting error. Anyone want to confirm?

    SQL Server 2005

    1) Run the following script:

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1)
    );

    2) Run the following script:

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO A SELECT '', ''

    That should produce the error:

    Msg 213, Level 16, State 1, Line 10
    Insert Error: Column name or number of supplied values does not match table definition.

    3) Run the following script:
    DROP TABLE A;

    4) Repeat steps 1 and 2.  No error is produced.

    5) Run the following script:

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1),
     B VARCHAR(1),
     C VARCHAR(1)
    );

    INSERT INTO A SELECT '', '', ''

    it should produce the same error.

    This can be repeated by adding or removing COLUMNs, as long as that number of COLUMNs has not been run before.

    I ran this on my SS 2005 Express, and a co-worker reproduced it on his.

    Anyway, it's an interesting error. Not a problem at all though.
    Monday, June 15, 2009 5:45 PM
    Moderator

Answers

  • Brian,

    If I read the T-SQL code correctly, you are exposing an issue which has existed for a very long time. And the issue has to do with the fact that the SQL is resolved (number of columns etc) before any statement in the batch has yet been executed (in the case where the table already exist). So, conaider below:

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1)
    );

    GO

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO A SELECT '', ''

    In the second batch, the table A already exist when the parser (etc) sees the T-SQL. It will (incorrectly in this case) resolve your INSERT statement against the existing table A. Is separating into more batches (adding GO) isn't an option, then dynamic SQL might be...


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, June 15, 2009 6:01 PM
    Moderator

All replies

  • Brian,

    If I read the T-SQL code correctly, you are exposing an issue which has existed for a very long time. And the issue has to do with the fact that the SQL is resolved (number of columns etc) before any statement in the batch has yet been executed (in the case where the table already exist). So, conaider below:

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1)
    );

    GO

    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO A SELECT '', ''

    In the second batch, the table A already exist when the parser (etc) sees the T-SQL. It will (incorrectly in this case) resolve your INSERT statement against the existing table A. Is separating into more batches (adding GO) isn't an option, then dynamic SQL might be...


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, June 15, 2009 6:01 PM
    Moderator
  • But why does it work the second time around (as mentioned in step 4 above)?
    Monday, June 15, 2009 6:05 PM
    Moderator
  • Can we communicate using TSQL, Brian? That would make it much easier for me to execute the code in a predictable manner. Say we start with below and you can modify below batch to show the behavior you are seing. Below show error for both 1 and 2. I'm no 2008. Is that what you are seeing, or do you see something else?

    --Setup
    IF OBJECT_ID('A') IS NOT NULL DROP TABLE A;
    GO
    DROP TABLE A;
    CREATE TABLE A( A VARCHAR(1));
    GO
    DROP TABLE A;
    CREATE TABLE A( A VARCHAR(1), B VARCHAR(1));
    INSERT INTO A SELECT '', '' --1
    GO
    DROP TABLE A;
    CREATE TABLE A( A VARCHAR(1));
    GO
    DROP TABLE A;
    CREATE TABLE A( A VARCHAR(1), B VARCHAR(1));
    INSERT INTO A SELECT '', '' --2
    GO

    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, June 15, 2009 6:46 PM
    Moderator
  • What you just posted works. Regardless this isn't a problem, just an interesting error.


    Tuesday, June 16, 2009 11:19 AM
    Moderator
  • If you feel like it, please send a repro. I don't mind looking at it, but I need to know how you separate the batches in order to have a prepducable case. Parsing rules in TSQL isn't always the most obvious, and it doesn't get easier as versions (and changes) comes and goes... :-)
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 16, 2009 11:24 AM
    Moderator
  • I highlight and hit [F5]. :)

    Tuesday, June 16, 2009 11:37 AM
    Moderator
  • I highlight and hit [F5]. :)


    :-)
    But that doesn't give me enought to go on. If you go back to your very first post, I can't just take all the text in there, highlight it an press F5 because there's a lot of words which aren't TSQL there. (I'm not trying to be a smart-Alec here, please don't take it the wrong way.) What I'm after is a TSQL script I can execute batch by batch (GO) from top to bottom which will give the result you describe. If you want to pursuit, that is... 
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 16, 2009 12:10 PM
    Moderator
  • Each snippet is separated by number.

    The problem with testing a script is that it only fails the first time for any given number of COLUMNs. To test it again, all the associated record numbers must be changed.


    This fails:

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1)
    );

    GO

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO B SELECT '', ''


    This works:

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1)
    );

    GO
    DROP TABLE B;
    GO

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO B SELECT '', ''


    Tuesday, June 16, 2009 12:30 PM
    Moderator
  • The key here is that we can produce a reproducable script since SQL Server do not "remember" anything between batches (except ofr whatever persisted object might exist, but we can run a separate batch first to drop it).
    In order so we can execute your script over and over again and make sure we agree on the behavior and then possibly explain it, I'm going to:
    - Add an batch to drop the table B if such exist as a separate batch
    - Change your "this works:" tect to GO so the immediate following is a separate batch
    - Add commets to let you know what I see and also so we can compare.

    Remember that parsing and object name resolving etc is basically done at the batch level. Bottom line is:
    - The first batch whith the INSERT will not execute the INSERT because the table exists at parse time but with incorrect number of columns.
    - The seconds batch with INSERT will execute the INSERT successfully because here the table doesn't exist when the batch is resolved so SQL Server look at the number of columns in your CREATE TABLE ststement to resolve that INSERT statement. The CREATE TABLE has two columns and so does the INSERT.

    *** Wow- hold my horses. I now see what you mean. Sorry for being daft. I will leave the text above (what I've written so far), even though I now realize that some of it is incorrect. I can execute the script once, and it behaves as per my explanation above. Now, that makes sense (in its own twisted way). But if I execute the script again, the first batch DO produce an INSERT. If I stop the database engine and start it again, we're back to basics again (probably what you get if you add yet another column to the script). I didn't expect this, and can only assume that the caching of IAM pages (and whatever) is what causes this weird behavior. This caching of pages I thought would only be tempdb, but apparently not (I also tested it in pubs). And in any event, the caching bit should only be to ehance performance and concurrency and should not "shine through" at the TSQL level in any way. I would consider opening a Connect entry on this...

    Just in case we ant to discuss this further, here's my current script:

    IF OBJECT_ID('B') IS NOT NULL DROP TABLE B;
    GO
    
    DROP TABLE B; --Error since table doesn't exist but doesn't terminate batch
    CREATE TABLE B
    (
     A VARCHAR(1)
    ); --Table is created with 1 column
    GO --Table now exist with 1 column
    
    -- Parsing: table B exist with only one column so the we will have an error for the INSERT statement
    DROP TABLE B; --Run-time: table is dropped
    CREATE TABLE B
    (
     A VARCHAR(1),
     B VARCHAR(1)
    ); --Run-time: table is created with two columns
    
    INSERT INTO B SELECT '', '' --Isn't included in run-time since parsing concluded that table structure doesn't match INSERT
    GO --Table now exist with 2 columns
    
    SELECT * FROM B --0 rows
    GO
    
    DROP TABLE B; --Run-time: Table dropped
    CREATE TABLE B --Run-time: Table is created with one column
    (
     A VARCHAR(1)
    );
    GO --Table exist with one column
    
    DROP TABLE B; --Run.time: Drops table
    GO --Table doesn't exist after this batch
    
    --Parsing: since table doesn't exist, the parser will look at the CREATE TABLE text and see that the INSER further down matches.
    DROP TABLE B;  --Error since table doesn't exist but doesn't terminate batch
    CREATE TABLE B
    (
     A VARCHAR(1),
     B VARCHAR(1)
    ); --Table is created with 2 columns
    
    INSERT INTO B SELECT '', '' --Run-time: This is now exeuted since parsing resolved the insert against the CREATE TABLE text.
    GO
    
    SELECT * FROM B --1 rows
    GO
    

    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, June 16, 2009 3:48 PM
    Moderator
  • Brian - I cannot reproduce it in SQL Server 2008. I get the same error all the time, except the very first time, no table to drop.

    Can you post a T-SQL script to reproduce the issue?

    Tibor's script produces the same errors as well.

    USE tempdb;
    
    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1)
    );
    
    GO
    DROP TABLE A;
    CREATE TABLE A
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );
    
    INSERT INTO A SELECT '', ''
    GO
    
    /*
    Msg 213, Level 16, State 1, Line 8
    Column name or number of supplied values does not match table definition.
    */



    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Tuesday, June 16, 2009 3:59 PM
    Moderator
  • Thanx for the post. Interesting error, no? :)
    Tuesday, June 16, 2009 4:05 PM
    Moderator
  • This script, posted above, should show it. But it only fails once. After that, add/remove a COLUMN to have a combination not used before, change the TABLE name, or as Tibor found, restart the DB.

    I found this in 2005. I have not tested it in 2008.

    This fails:

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1)
    );

    GO

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO B SELECT '', ''


    This works:

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1)
    );

    GO
    DROP TABLE B;
    GO

    DROP TABLE B;
    CREATE TABLE B
    (
     A VARCHAR(1),
     B VARCHAR(1)
    );

    INSERT INTO B SELECT '', ''
    Tuesday, June 16, 2009 4:07 PM
    Moderator
  • OK I reproduced it, failed only the first time with SQL Server 2008.

    Here is the point though, in production scripts we would not use virgin DROP TABLEs.

    We would use conditional DROP:

    IF

     

    EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[B]') AND type in (N'U'))

    DROP TABLE [dbo].[B]

    GO


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com
    Tuesday, June 16, 2009 5:44 PM
    Moderator
  • I know. I would never such a thing in production.

    Anyway, it's an interesting error. Not a problem at all though.
    Tuesday, June 16, 2009 6:21 PM
    Moderator
  • Hello,

    I am getting similar error something like this.

    "Insert Error: Colun name or number of supplied values does not match table definition."

    I got above error while executing following line.

    accountAdapter.Update(TDS,

    "BP_ACCOUNT");

    The command text of insert command of above data adapter is as given below.


    INSERT INTO BP_ACCOUNT WITH (ROWLOCK) (LINKED_ACCOUNT_K, INTEREST_ACCRUAL_ACCOUNT_K, INTEREST_ACCOUNT_K, ACCOUNT_N, ACCOUNT_NUMBER_C, LINKED_INDICATOR_C, LINKED_ACCOUNT_TYPE_C, NEW_ACCOUNT_F, CENTER_K, COMPANY_K, FORMULA_REFERENCE_ID_X, CLASSIFICATION_K, INTEREST_BEARING_F, INTEREST_PERIOD_K, STATE_TAX_EXEMPT_PERCENT_R, FEDERAL_TAX_EXEMPT_R, LOCAL_TAX_EXEMPT_PERCENT_R, ECONOMIC_VALUE_METHOD_K, CASH_FLOW_TYPE_K, ACCOUNT_TYPE_K, CATEGORY_TYPE_K, VALID_F, LST_CHNG_D, LST_CHNG_USER_K, LST_CHNG_ACTN_C) VALUES (@LINKED_ACCOUNT_K, @INTEREST_ACCRUAL_ACCOUNT_K, @INTEREST_ACCOUNT_K, @ACCOUNT_N, @ACCOUNT_NUMBER_C, @LINKED_INDICATOR_C, @LINKED_ACCOUNT_TYPE_C, @NEW_ACCOUNT_F, @CENTER_K, @COMPANY_K, @FORMULA_REFERENCE_ID_X, @CLASSIFICATION_K, @INTEREST_BEARING_F, @INTEREST_PERIOD_K, @STATE_TAX_EXEMPT_PERCENT_R, @FEDERAL_TAX_EXEMPT_R, @LOCAL_TAX_EXEMPT_PERCENT_R, @ECONOMIC_VALUE_METHOD_K, @CASH_FLOW_TYPE_K, @ACCOUNT_TYPE_K, @CATEGORY_TYPE_K, @VALID_F, @LST_CHNG_D, @LST_CHNG_USER_K, @LST_CHNG_ACTN_C);SELECT SCOPE_IDENTITY() AS ACCOUNT_K


    Also the design structure of the table in whilch I am inserting record is as given below.

    CREATE

     

    TABLE [dbo].[BP_ACCOUNT](

    [ACCOUNT_K] [int]

    IDENTITY(1,1) NOT NULL,

    [LINKED_ACCOUNT_K] [int]

    NULL,

    [INTEREST_ACCRUAL_ACCOUNT_K] [int]

    NULL,

    [INTEREST_ACCOUNT_K] [int]

    NULL,

    [ACCOUNT_N] [nvarchar]

    (70) NOT NULL,

    [ACCOUNT_NUMBER_C] [nvarchar]

    (16) NOT NULL,

    [LINKED_INDICATOR_C] [int]

    NULL DEFAULT ((1)),

    [LINKED_ACCOUNT_TYPE_C] [int]

    NULL,

    [NEW_ACCOUNT_F] [bit]

    NULL DEFAULT ((0)),

    [CENTER_K] [int]

    NULL,

    [COMPANY_K] [int]

    NULL,

    [FORMULA_REFERENCE_ID_X] [nvarchar]

    (20) NULL,

    [CLASSIFICATION_K] [int]

    NULL,

    [INTEREST_BEARING_F] [bit]

    NOT NULL DEFAULT ((1)),

    [INTEREST_PERIOD_K] [int]

    NULL DEFAULT ((1)),

    [FEDERAL_TAX_EXEMPT_R] [float]

    NULL DEFAULT ((0)),

    [STATE_TAX_EXEMPT_PERCENT_R] [float]

    NULL DEFAULT ((0)),

    [LOCAL_TAX_EXEMPT_PERCENT_R] [float]

    NULL DEFAULT ((0)),

    [ECONOMIC_VALUE_METHOD_K] [int]

    NULL DEFAULT ((3)),

    [CASH_FLOW_TYPE_K] [int]

    NULL DEFAULT ((1)),

    [ACCOUNT_TYPE_K] [int]

    NOT NULL,

    [CATEGORY_TYPE_K] [int]

    NULL,

    [VALID_F] [bit]

    NULL DEFAULT ((1)),

    [LST_CHNG_D] [datetime]

    NOT NULL,

    [LST_CHNG_USER_K] [int]

    NOT NULL,

    [LST_CHNG_ACTN_C] [tinyint]

    NOT NULL DEFAULT ((1)),

     

    CONSTRAINT [XPKBP_ACCOUNT] PRIMARY KEY CLUSTERED

    (

    [ACCOUNT_K]

    ASC

    )

     

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

     

    ON [PRIMARY]



    PLease help me..........

    Regards, Denish Kanabar
    Tuesday, October 27, 2009 12:20 PM
  • Denish, it is appropriate to start a new thread for a new problem, even if it is related. Including a link to the old thread is nice too.
    Tuesday, October 27, 2009 2:50 PM
    Moderator