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.