CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Pr0no
Pr0no

Reputation: 4099

How do I properly access temp tables created in another database?

Please consider the following:

use db1;

select * into #db1_tmp from mytable;

use db2;

select * into #db2_tmp from myothertable;

-- join temp table 1 and 2

select * from #db1_tmp a
left join db2_tmp b on where a.uid = b.uid;

This works, but SQL Server Management Studio is red-underlining #db1_tmp in the last query and therefore in all other statements that depend on this table.

Question: what is the proper way to access a temp table created in another database to prevent this underlining from happening? I tried db1.#db1_tmp but this does not work. I'm on SQL Server 2008.

Upvotes: 1

Views: 6595

Answers (2)

Recursive
Recursive

Reputation: 952

Temp tables has no use of Database reference for creation so gets created in tempdb. Only the source can be changed with "use" or "dbname.dbo.mytable".

The red-underlining is due to intellisense. The temp table is just identified as a normal table before execution and redlined due to database change.

Note: The select query at the last has syntax errors. it should be,

select * from #db1_tmp a left join #db2_tmp b on a.uid = b.uid;

Upvotes: 0

alroc
alroc

Reputation: 28154

Temp tables actually appear in their own database, TempDB. I think the root of your issue is the use statements. Try this instead:

select * into #db1_tmp from db1.dbo.mytable;
select * into #db2_tmp from db2.dbo.myothertable;

-- join temp table 1 and 2

select * from #db1_tmp a
left join db2_tmp b on where a.uid = b.uid;

But if this is the extent of what you're doing (creating the temp tables just to do a join across the databases), you can skip the temp tables altogether:

select * from db1.dbo.mytable a join db2.dbo.myothertable b on a.uid = b.uid.

Upvotes: 2

Related Questions