Table Columns dont' match when using a loop to add columns later?!

  • Hi All,

    I have a piece of code which does the following (assume tbl1 already exists);

    1. Create tbl2 with 3 columns

    2. Insert data from tbl1 into the 3 columns in tbl2

    3. Loop round the number of variables to dynamically add columns onto tbl2

    I know stage 3 works by itself.

    As do stages 1 & 2 when stage 3 is commented out.

    However, when I try to run all together stages 1 & 2 complete successfully but as soon as the code enters the loop I get the following error and I cannot understand why, or how I can work around it?

    Server: Msg 213, Level 16, State 4, Line 172

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

    Any help you guys can give me would be appreciated.

  • Same class?

    http://www.sqlservercentral.com/Forums/Topic406041-9-1.aspx#bm406087


    N 56°04'39.16"
    E 12°55'05.25"

  • In most cases it isn't a good idea to add columns dynamically to a table - but it can be useful sometimes when creating a report, so I'll skip that rant.

    I suppose that your problem is, that at the beginning the entire batch is checked and evaluated by optimizer. At this moment, it does not take into account any branching (conditional execution of code) and considers the SQL as a whole.

    Try adding GO after your second part, to break down the process into two separate parts. It should help, but if you declare variables that you need to use in both parts, that will be a problem, because the declaration only works for the batch. You might solve this for example by putting the values of variables into temporary table and read them from there.

  • Temp Table?

  • I actually solved this in the end.

    My solution was to exchange steps 2 & 3 around. This meant I was dynamically creating the overall structure of the table first and the adding in the data for the first 3 columns.

    Thanks for all your comments.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply