Here is even a better work-around - execute the ALTER code in an EXEC() statement, and the new columns are available to you!


>Anoter work-around that I have discovered is to do your original
>SELECT * INTO ##tmptable FROM table1
>ALTER ##tmptable ADD new_column char(1)
>
>and then
>SELECT * INTO ##tmpTableToUse FROM ##tmptable
>DROP ##tmptable
>
>In this new table ##tmpTableToUse, the field new_column is available to you! This requires some extra processing on SQL Server's part, but it might save in code maintenance.
>
>KB Article http://support.microsoft.com/default.aspx?scid=kb;EN-US;q295305 points out that the original ALTER statement won't work unless the new field is referenced in a nested stored procedure.
>
>Hope this helps!
>
>>If you have access to the SQL Server 2000 Query Analyzer, you can have it generate the column list for you. Use the Object Browser windows and drill down to your table. Then right-click, choose Script Object To Clipboard As, and then choose INSERT and then you can do the same thing with SELECT.
>>
>>-Mike
>>
>>>I am really trying to avoid that, as I have a lot of tables and a lot of fields names to do this with.
>>>Thanks,
>>>Darren.