#Temp Table
-
Hello,
I have created a temp table in the northwind database i can not locate it. Are the temp tables located in a different area?
Thanks,
Erik....
Dam again!
-
erik...never forget BOL...if you do a "select * from sysobjects where name like '#%'" - or even simply "order by name" you should see your table...
- do this in your tempdb database!
BOL:
"Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.
The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL Server™ 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.
For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.
**ASCII stupid question, get a stupid ANSI !!!** -
Temp tables cannot be in Northwind, Temp Tables are in TempDB an intermediate place to test code and also run long and complex queries. There are two types of Temp Tables the one with the single # is local and the one with double ## is global. The global is resource intensive so should be used only when needed. It is good practice to drop both explictly in your code. Run a search for Temp Table in the BOL (books online). Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie -
Thank you for the replys!
Erik...
Dam again!
-
Here is the #temp table that i am working with.
Please keep in mind that this is not for use other than i am just learning how to work with #temp tables. When ever i run this querry the analyer tells me that 94 rows are affected.. while still connected i go to the enterprise manager and try to find the #table in the tempdb; however, i do not see it. Am i missing something here are is this not the correct way to find the #table?
--Create a temp table to hold the current page of data
--Add and ID column to count the records
CREATE TABLE #TempTable_eriks
(
ID int IDENTITY PRIMARY KEY,
CompanyName nvarchar(40),
ContactName nvarchar (30),
ContactTitle nvarchar (30),
Phone nvarchar (24),
Fax nvarchar (24)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable_eriks
(
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
)
SELECT
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
FROM
Customers
Dam again!
-
afaik - temp tables are transient by nature and definition and the only way you can view the data in temp tables is by doing a "select *..." in QA!
Or you can have a temp table in a stored procedure that temporarily stores values and displays them to the end user...
here's some more info from BOl on the tempdb database...
tempdb
tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.
**ASCII stupid question, get a stupid ANSI !!!** -
thanks..
Dam again!
-
Last thing to add to find a temp table :
if Object_id(tempdb..#tempTable)>0
--it exists
else
--it's been destroyed somehow
-
Temp table are only visible to the connection that created it (except ##temp tables), so you won't be able to see it in EM. Remi's code won't work either on a different connection. You'll have to do this:
select * from tempdb..sysobjects
and look for the table:
#TempTable_eriks_________________________________23423
or something like that.
Dylan Peters
SQL Server DBA -
which's why I had suggested.."select * from sysobjects where name like '#%'"
**ASCII stupid question, get a stupid ANSI !!!** -
TEMPDB..sysobjects!!!!
-
agreed rg - i had also said "- do this in your tempdb database!"..
**ASCII stupid question, get a stupid ANSI !!!** -
That's the proof that peeps don't always read long posts... that's why I overqualify objects sometimes
. -
peeps.. qu'est-ce que c'est - oh - people...no wonder you get your posts ahead of everyone else - you just use too many abbreviations...
**ASCII stupid question, get a stupid ANSI !!!** -
Seriously Sue... 2 more characters in 13 minutes??? I think I can handle that.
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply