temp table problem - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

temp table problem

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-08-06 : 08:46:23

Hi,

i'm trying to store the values from a sql search select statement into a temp table but am having problems! I've pasted my code below.

if exists (select * from dbo.sysobjects where id = object_id(N'#TempTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #TempTable
GO

create table #TempTable (
Studio varchar(20),
Film varchar(50),
Country varchar(25),
Classification varchar(30),
Element varchar(20),
CatalogueNo varchar(20),
PackCode varchar(20),
QtyOrdered int,
totalQtyOrdered int,
TMJobNumber int,
RequestedDeliveryDate datetime,
PlannedDeliveryDate datetime
)

SELECT
Films.Studio,
Films.Film,
CountriesLookup.Country,
ClassificationLookup.Classification,
ElementLookup.Element,
Jobs.CatalogueNo,
Jobs.PackCode,
JobDelivery.QtyOrdered,
Jobs.QtyOrdered AS totalQtyOrdered,
Jobs.TMJobNumber,
JobDelivery.RequestedDeliveryDate,
JobDelivery.PlannedDeliveryDate

INTO #TempTable

FROM
Jobs INNER JOIN
BOM ON Jobs.BOMID = BOM.BOMID INNER JOIN
Films ON BOM.FilmID = Films.FilmID INNER JOIN
CountriesLookup ON BOM.CountryID = CountriesLookup.CountryID INNER JOIN
ClassificationLookup ON BOM.ClassificationID = ClassificationLookup.ClassificationID INNER JOIN
ElementLookup ON BOM.ElementID = ElementLookup.ElementID INNER JOIN
JobDelivery ON Jobs.TMJobNumber = JobDelivery.TMJobNumber
WHERE
jobs.jobtype in ( 'print', 'repro')
and films.film like '%Spy%'

I get the following error message when i run it

(1 row(s) affected)

Server: Msg 2714, Level 16, State 1, Line 34
There is already an object named '#TempTable' in the database.

can anyone please help me? Also i would like at the end to display the result set from the temp table. How do i do this? Any help greatly appreciated. Also is the syntax correct?

Thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-06 : 08:48:31

Temp tables exist in tempdb, no the database you are working in. So you drop is not executing. You really shouldn't have to check if a temp table exists, you should know already.

Jay White
{0}

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-08-06 : 08:49:51

Jay, I don't think that is the problem, although I agree with you.

zubair, use insert into instead of select into, and it should work.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-06 : 09:59:32

Try this:


IF OBJECT_ID('tempdb..#TempTable') > 0 
BEGIN
    DROP TABLE #TempTable
    GO
END

--Rest of SProc here

This usually works, but I am not quite sure if it works all the time

Owais

zubair
Yak Posting Veteran

67 Posts

Posted - 2003-08-06 : 11:46:30

thanks for the help here

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-07 : 09:10:24

quote:
Originally posted by mohdowais

Try this:

IF OBJECT_ID('tempdb..#TempTable') > 0
BEGIN
DROP TABLE #TempTable
GO
END

--Rest of SProc here

This usually works, but I am not quite sure if it works all the time

Owais

Yes, this will work everytime I think. Providing that you specify the correct name of the temp table, the function OBJECT_ID will return the ID number of the table.

Thanks for this! I will use it instead of my lame method from now on

----------------
Shadow to Light

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources