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]
