Are you saying that it makes no difference whether you use local or temporary tables?
If so, did you get to the bottom of the original problem? Spike > -----Original Message----- > From: Justin MacCarthy [mailto:[EMAIL PROTECTED]] > Sent: 25 September 2002 16:04 > To: [EMAIL PROTECTED] > Subject: [ cf-dev ] adventures in temp tables was RE: [ > cf-dev ] Temp tables in SQL Server > > > Well here you are folks. Some info. > > > Using Global temporary table names with a double number sign > (##table_name) all these queries work > > > <cftransaction> > <cfquery datasource="#request.dsn#" name="CreateTable"> > CREATE TABLE #####CFID#x#CFTOKEN# (WhateverID > INT PRIMARY KEY) > > DROP TABLE #####CFID#x#CFTOKEN# > </cfquery> > </cftransaction> > > > <cftransaction> > > <cfquery datasource="#request.dsn#" name="CreateTable"> > CREATE TABLE #####CFID#x#CFTOKEN# (WhateverID > INT PRIMARY KEY) > </cfquery> > > <cfquery datasource="#request.dsn#" name="DropTable"> > DROP TABLE #####CFID#x#CFTOKEN# > </cfquery> > </cftransaction> > > > <cftransaction> > > <cfquery datasource="#request.dsn#" name="CreateTable"> > CREATE TABLE #####CFID#x#CFTOKEN# (WhateverID > INT PRIMARY KEY) > </cfquery> > <cfquery datasource="#request.dsn#" name="intotable"> > insert into #####CFID#x#CFTOKEN# > (WhateverID) > Values > (1) > </cfquery> > <cfquery datasource="#request.dsn#" name="gettable"> > Select * FROM #####CFID#x#CFTOKEN# > </cfquery> > <cfquery datasource="#request.dsn#" name="CreateTable"> > DROP TABLE #####CFID#x#CFTOKEN# > </cfquery> > </cftransaction> > > <cfoutput query="gettable"> > #WhateverID# > </cfoutput> > > > Prefix local temporary table names with single number sign > (#table_name), and prefix all these work > > <cftransaction> > <cfquery datasource="#request.dsn#" name="CreateTable"> > CREATE TABLE ###CFID#x#CFTOKEN# (WhateverID INT > PRIMARY KEY) > > DROP TABLE ###CFID#x#CFTOKEN# > </cfquery> > </cftransaction> > > so does this > > <cftransaction> > > <cfquery datasource="#request.dsn#" name="CreateTable"> > CREATE TABLE ###CFID#x#CFTOKEN# (WhateverID INT > PRIMARY KEY) > </cfquery> > > <cfquery datasource="#request.dsn#" name="DropTable"> > DROP TABLE ###CFID#x#CFTOKEN# > </cfquery> > </cftransaction> > > and this > > <cftransaction> > > <cfquery datasource="#request.dsn#" name="CreateTable"> > CREATE TABLE ###CFID#x#CFTOKEN# (WhateverID INT > PRIMARY KEY) > </cfquery> > <cfquery datasource="#request.dsn#" name="intotable"> > insert into ###CFID#x#CFTOKEN# > (WhateverID) > Values > (1) > </cfquery> > <cfquery datasource="#request.dsn#" name="intotable"> > insert into ###CFID#x#CFTOKEN# > (WhateverID) > Values > (2) > </cfquery> > <cfquery datasource="#request.dsn#" name="gettable"> > Select * FROM ###CFID#x#CFTOKEN# > </cfquery> > <cfquery datasource="#request.dsn#" name="CreateTable"> > DROP TABLE ###CFID#x#CFTOKEN# > </cfquery> > </cftransaction> > > <cfoutput query="gettable"> > #WhateverID# > </cfoutput> > > > > > > -----Original Message----- > > From: Spike [mailto:[EMAIL PROTECTED]] > > Sent: 25 September 2002 14:34 > > To: [EMAIL PROTECTED] > > Subject: RE: [ cf-dev ] Temp tables in SQL Server > > > > > > Aren't you creating a local temp table with the create table > > statement? > > > > Will that table be available to the second query, or do you need to > > use #####cfid#_#cftoken#? > > > > Spike > > > > > -----Original Message----- > > > From: Justin MacCarthy [mailto:[EMAIL PROTECTED]] > > > Sent: 25 September 2002 15:22 > > > To: [EMAIL PROTECTED] > > > Subject: [ cf-dev ] Temp tables in SQL Server > > > > > > > > > Hi, > > > Anyone used temp tables in SQL Server in <cfquery>? > > > Anyone see anything wrong with doing below? > > > > > > <cftransaction> > > > <cfquery datasource="dsn" name="CreateTable"> > > > CREATE TABLE ##CFID#_#CFTOKEN#(WhateverID INT > > > PRIMARY KEY) > > > </cfquery> > > > <cfquery datasource="dsn" name="y"> > > > INSERT INTO ##CFID#_#CFTOKEN# > > > SELECT WhateverID > > > FROM Whatever > > > > > > </cfquery> > > > <cfquery datasource="dsn" name="x"> > > > SELECT * > > > FROM Whatever2 > > > WHERE Whatever2.WhateverID IN > > > ( SELECT WhateverID FROM ##CFID#_#CFTOKEN#) > > > </cfquery> > > > <cfquery datasource="dsn" name="DropTable"> > > > DROP TABLE ##CFID#_#CFTOKEN# > > > </cfquery> > > > </cftransaction> > > > > > > Cheers > > > Justin > > > > > > -- > > > ** Archive: > > > http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > > > > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > > For additional commands, e-mail: [EMAIL PROTECTED] > > > For human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > [EMAIL PROTECTED] For > > human help, e-mail: [EMAIL PROTECTED] > > > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
