Temporary Table Name Re-Use Challenge

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Temporary Table Name Re-Use Challenge

Author

Topic

Pomm
Starting Member

1 Post

Posted - 2010-09-23 : 03:23:12

Im running a table comparison script that goes out & checks for two instances of the same table in two diff db's and if there are any differences, then it spits them out....So far so good.

The challenge at home is if Im running more than one table comparison scripts as shown below, it is throwing me an error that the Temporary table defined already exists although Im dropping any reference to the previously used temporary table before I do anything.
Not sure as how to get Past this and hence Im restricting myself to run only one query at any one time. However there are over 200 tables that I wrote this kind of scripts for and it would be quite a long process to go around & highlight the query & run it and repeat the same all over.

Any ideas or suggestions to get around this ? Here are the queries -

------------------------------------------------------------------------------------------------------------------------------------------

If Object_id('tempdb.dbo.#Reporting_QA) is not null Begin Drop table #Reporting_QA End
If Object_id('tempdb.dbo.#OLTP_QA') is not null Begin Drop table #OLTP_QA End
Use OLTP_qa;
select id, name into #Reporting_QA from table1
Use Reporting_qa;
select id, name into #OLTP_QA from table1
Select min(Env) as Env, ID, Name
from (
select 'Reporting' as Env, id, name from #Reporting_QA
union all
select 'OLTP' as Env, id, name from #OLTP_QA
)AA group by Env, ID, Name
having count(*) = 1

If Object_id('tempdb.dbo.#Reporting_QA) is not null Begin Drop table #Reporting_QA End
If Object_id('tempdb.dbo.#OLTP_QA') is not null Begin Drop table #OLTP_QA End
Use OLTP_qa;
select id, name into #Reporting_QA from table2
Use Reporting_qa;
select id, name into #OLTP_QA from table2
Select min(Env) as Env, ID, Name
from (
select 'Reporting' as Env, id, name from #Reporting_QA
union all
select 'OLTP' as Env, id, name from #OLTP_QA
)AA group by Env, ID, Name
having count(*) = 1

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 04:28:20

Hi Pomm,

Inside the batch the preprocessor checks for a few things. I think this statement is causing the problem:


select id, name into #Reporting_QA from table1

If you add a GO statement to....


If Object_id('tempdb.dbo.#Reporting_QA') is not null Begin Drop table #Reporting_QA End
If Object_id('tempdb.dbo.#OLTP_QA') is not null Begin Drop table #OLTP_QA End
GO

Use OLTP_qa;
select id, name into #Reporting_QA from table1
Use Reporting_qa;
select id, name into #OLTP_QA from table1
Select min(Env) as Env, ID, Name
from (
select 'Reporting' as Env, id, name from #Reporting_QA 
union all
select 'OLTP' as Env, id, name from #OLTP_QA 
)AA group by Env, ID, Name
having count(*) = 1

GO

If Object_id('tempdb.dbo.#Reporting_QA') is not null Begin Drop table #Reporting_QA End
If Object_id('tempdb.dbo.#OLTP_QA') is not null Begin Drop table #OLTP_QA End
GO

Use OLTP_qa;
select id, name into #Reporting_QA from table2
Use Reporting_qa;
select id, name into #OLTP_QA from table2
Select min(Env) as Env, ID, Name
from (
select 'Reporting' as Env, id, name from #Reporting_QA 
union all
select 'OLTP' as Env, id, name from #OLTP_QA 
)AA group by Env, ID, Name
having count(*) = 1

Does that help?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 04:29:35

Hi again.

What are you actually trying to do? I wrote a little script to check for schematic differences between databases (so different column names, datatypes etc)

IT's here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136699

Don't know if it will be useful to you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources