|
Home > Archive > MS SQL Server > August 2005 > temp table problem and needs work around
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
temp table problem and needs work around
|
|
| harvinder 2005-08-08, 11:23 am |
| Hi,
We have a query batch like the following:
if object_id('tempdb..#tmp') is not null drop table #tmp
select * into #tmp from tab1
.............................
This particular query batch can be executed once or more than 1 time in a
single transaction. Problem is that when it is executed second time in the
same transaction we get the error:
#tmp already exists...since this is normal behavior of sql server that we
can't create the tmp table with same name in same transaction as it will do
compile time name resolution for temp tables.
In this case we need to use the temp tables. How can we solve this issue?
Thanks
--Harvinder
| |
| Alejandro Mesa 2005-08-08, 11:23 am |
| create table #tmp (c1 ...)
insert into #tmp(c1, ..., cn)
select c1, ..., cn from t1
truncate table #tmp
insert into #tmp(c1, ..., cn)
select c1, ..., cn from t2
....
drop table #t
AMB
"harvinder" wrote:
> Hi,
>
> We have a query batch like the following:
> if object_id('tempdb..#tmp') is not null drop table #tmp
> select * into #tmp from tab1
> ............................
> This particular query batch can be executed once or more than 1 time in a
> single transaction. Problem is that when it is executed second time in the
> same transaction we get the error:
> #tmp already exists...since this is normal behavior of sql server that we
> can't create the tmp table with same name in same transaction as it will do
> compile time name resolution for temp tables.
> In this case we need to use the temp tables. How can we solve this issue?
>
> Thanks
> --Harvinder
>
| |
| harvinder 2005-08-08, 11:23 am |
| This will not work since we have a single batch that can be executed >1
times....so i have to drop and create the table again ...i can't decide on
truncate or drop inside the batch
"Alejandro Mesa" wrote:
[color=darkred]
> create table #tmp (c1 ...)
>
> insert into #tmp(c1, ..., cn)
> select c1, ..., cn from t1
>
> truncate table #tmp
>
> insert into #tmp(c1, ..., cn)
> select c1, ..., cn from t2
> ...
>
> drop table #t
>
>
> AMB
>
>
> "harvinder" wrote:
>
|
|
|
|
|