Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sherrer
64 Posts |
 Posted - 2003-01-10 : 11:23:49
|
| I am using V7 SP3Within a stored procedure I have created a temp table and have inserted data into the temp table. I use the ALTER TABLE command to add a column to the table. Then while attempting to update the column, I get an error stating that the column does not exist. I have tried putting it inside a transaction with no luck. I know in query analyser I can use "GO" to create a seperate batch, but this doesn't work in the stored procedure. How do I commit the change to the temp table?Thanks... Kevin |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-10 : 11:49:48
|
| The SP gets the structure of tables when it is loaded.This includes temp tables which it gets from the create.No way round this.You can do the alter table then call another SP for the rest of the processing.This SP can access the temp table and will be compiled at run time (every time) and get the run time structure.You can do a select * from the first SP and get the new structure too.see www.nigelrivett.comAccess Temp Tables Across SPs==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sherrer
64 Posts |
Posted - 2003-01-10 : 12:00:39
|
| Thanks for the info.My easiest way around the problem is to create a second relational temp table and insert my values into it.Thanks again,Kevin |
|
|
|
verronep
Starting Member
15 Posts |
Posted - 2003-01-10 : 12:16:38
|
| Why can't you just create the first temp table with the extra column, and leave the values null until you need it? I'm assuming you are dropping the temp table when you're done with the SPROC?"I have not failed. I have just found 10,000 ways that won't work." - Thomas Alva Edison (1847-1931) |
|
|
|
sherrer
64 Posts |
Posted - 2003-01-10 : 13:48:32
|
| I didn't make that obvious did I. I am doing a bulk insert into the temp table, and I don't feel like fooling with a format file.Kevin |
|
|
|
|
|
|
Trouble with ALTER TABLE in SPROC