November 4, 2004 at 5:55 pm
Hi,
If i create a temporary table with create table #mytemptable(blah blah blah) in a Coldfusion app, how do I check to see if the table exists, and drop it, before I create it?
The code I would use for a normal table, does not work:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mytable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[mytable]
GO
I suppose it has something to do with where the temp table is created, and its table type......
November 4, 2004 at 7:05 pm
I do not use Coldfusion, but for SQL this is not as pretty, nor as specifc as the common code you are currently using. But it will work.
CREATE TABLE #TEST( ColumnA int,
ColumnB varchar(25))
INSERT INTO #TEST
SELECT 1, 'TEST 1'
INSERT INTO #TEST
SELECT 2, 'TEST 2'
SELECT * FROM #TEST
IF EXISTS( SELECT * FROM sysobjects WHERE name = '#TEST')
DROP TABLE [#TEST]
--GO
SELECT * FROM #TEST
Incidentally, #TempTables exist in TempDB and are destroyed once the SPID ends...
I wasn't born stupid - I had to study.
November 4, 2004 at 7:22 pm
Change
IF EXISTS( SELECT * FROM sysobjects WHERE name = '#TEST')
To
IF object_id('tempDB..#TEST') IS NOT NULL
The reason is it is only referencable in tempDB either way and the name in sysobjects will be something more like
#TEST______________________________________________________________________________________12313121
Which could actually belong to another connected user, and means you have to give rights to the user to read from the table, and if is for another connected user the DROP TABLE value will throw and error.
The change I list is safest method.
Now as for checking, the key is that the secound the connection drops so does the table so make sure between executions do not drop the connection (which the server will tend to do between runs unless you cache into a session style object which is a bad idea from a performance and threading standpoint) until you are thru doing all things with the object. It is either better to wrap logic in a stored procedure or use a way to use a static table where the users data is identifiable (which means you have to come up with a good cleanup schema and ensure they are uniquely identifiable on the server).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply