Remove Temp Table 3

[UncleCake]

I am attempting to do my first temp table. I created one, but forgot to drop it. Now when I try to create it again in the query analyzer like this I get a message that says "There is already an object named '#tmpScannerInvoiceItems' in the database."

create table dbo.#tmpScannerInvoiceItems (InvoiceItems char(6),Location char(10))
SELECT DISTINCT Invoice, Locationinto dbo.#tmpScannerInvoiceItemsFROM         dbo.InvoiceItems WHERE     (Qty = 1)
select * from dbo.#tmpScannerInvoiceItems order by location desc
drop table dbo.#tmpScannerInvoiceItems

I then tried to just do this it says "Cannot drop the table '#tmpScannerInvoiceItems', because it does not exist in the system catalog."

drop table dbo.#tmpScannerInvoiceItems

Before trying to use the "drop table" line, I deleted the temp table from ObjectBrowser >> temppdb in the query analyzer. I think that this is were I messed up. What can I do to get this to work again?

 

[Rhys666]

Initially, Close and Re-Open Query Analyzer and see if that helps.

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!

 

[JamesLean]

Firstly, you don't need to qualify temp table names with the owner (dbo).

As a quick and dirty way to clean up in this situation just close the connection in QA and start a new one.

--James

 

[UncleCake]

I closed QA, which I assume closes the connection, but it is still doing the same thing. (I also close the Enterprise Manager just to see if helped, but it didn't)

 

[vongrunt]

One trick:

if object_id('tempdb..#blah') is not null
	drop table #blah

create table #blah( ... )

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[UncleCake]

I think that I am creating the table incorrectly or inserting data into it incorrectly. If I use the following it completes the executes successfully. Therefore, it is only happening when I am trying to insert into the temp table. Is there something wrong with the select and insert into part?

create table #tmpScannerInvoiceItems (
Invoice char(6),
Location char(10)
)

SELECT InvoiceItems.Invoice, InvoiceItems.Location
--into #tmpScannerInvoiceItems <LINE IS COMMENTED OUT
FROM         InvoiceItems 


select * from #tmpScannerInvoiceItems order by location desc

drop table #tmpScannerInvoiceItems

 

[vongrunt]

SELECT INTO works only if target table does not exist at runtime.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLBill]

Check the BOL for SELECT INTO and INSERT.

SELECT INTO is used when you want the SELECT to create a new table. However, you already created the table...so you need to do an INSERT.

INSERT INTO #tmpScannerInvoiceItems (Invoice, Location)
  SELECT Invoice, Location
  FROM InvoiceItems

-SQLBill

Posting advice: FAQ481-4875

 

[UncleCake]

Not knowing all of the terms(does not exist at runtime), are you saying I can't do a SELECT INTO my temp table. If so, how would I add items from one table into a temp table and work with that data?

 

[vongrunt]

Use INSERT INTO... SELECT instead.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JayKusch]

Lets try this ...

create table #tmpScannerInvoiceItems (
Invoice char(6),
Location char(10)
)
INSERT INTO #tmpScannerInvoiceItems (Invoice, Location)

     SELECT Invoice, Location
     FROM   InvoiceItems


select * from #tmpScannerInvoiceItems order by location desc

drop table #tmpScannerInvoiceItems

Thanks

J. Kusch

 

[SQLBill]

Amazing vongrunt and Jay....just what I suggested

-SQLBill

Posting advice: FAQ481-4875

 

[UncleCake]

That did it guys!!!

I didn't know that I could do that. Thank you for all of your help, I really appreciate it!!!


I just wanted to confirm, if I have multiple users accessing a SP that creates the temp table, each user will have its own table so they can be working sinoutaisly, each with their own connection?That did it guys!!!

I didn't know that I could do that. Thank you for all of your help, I really appreciate it!!!


I just wanted to confirm, if I have multiple users accessing a SP that creates the temp table, each user will have its own table so they can be working at the same time, each with their own connection?


-Uncle Cake

 

[UncleCake]

SQLBill,

I am sorry, I guess I overlooked what you actually wrote. Please accept my apologies!!!

-Uncle Cake

 

[SQLBill]

That's okay...I just found it humorous...that's why I included the laughing smiley.

Besides, since three responders provided the same answer, it really should show to you (the poster) that the answer provided was the correct one.

To answer your question:

if I have multiple users accessing a SP that creates the temp table, each user will have its own table so they can be working at the same time, each with their own connection?

Not necessarily. What permission does the SP run as? If it runs as the user, then you will have separate tables for each user:
sqlbill.#tablename
vongrunt.#tablename
etc....
But if the SP creates the table as dbo then you can only have one temp table with that name. However, once it's dropped another user can recreate it.

BTW-vongrunt's comment about 'at runtime' just means that you need to remember a script is run a portion at a time. In your script, when it first runs the table doesn't exist. Then it creates the table. But then you [run] the SELECT INTO, well the table already exists at that point [runtime] so SELECT INTO fails.

Hope that clarified the term runtime.

-SQLBill



Posting advice: FAQ481-4875

 

[UncleCake]

I am just using a connection string with a user name and pwd that would work across the board. It is most likely there won't be multiple users, but there could be that chance.

Is there anyway to get around this?

Thanks for the clarification on the runtime.

 

[vongrunt]

Don't get confused with temp table name. Temp tables have session (connection) scope, and server internally handles them with different names, as seen with:

select * 
from tempdb..sysobjects
where xtype = 'U'

In other words: many users can create the same temp table simultaneously.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[UncleCake]

I want to use the temp table to assist me with sorting. I am pulling information out of two tables and sorting it in the temp table. I am not sure I am doing it correctly, but I can try to explain it more, I would love to get your feedback. I started this yesterday, but I was having problems getting it completed.

 

[Catadmin]

Just a note. When I've created temp tables in SPs and forgotten to drop them, I copy-n-paste the SP's text into QA, set up lines which drop the SP, create the SP (after adding a line at the start which says "Drop Table #TempTableName" and adding the same line at the end of the SP), add an Exec SPName as the last line of code, put GO between everything and then run the code.

Afterwards I go back to the top, then delete the first "Drop" line, leaving the one at the end, and re-run the Drop/Create/Exec statements again.

I tend to do this while I'm testing, though. You want to be really careful about doing all the drop & adds of Procs in a production environment. Vongrunt's "If object_ID..." code above is excellent if you can't drop & recreate the procedure.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???