How to Create a Temporary Table with SQL Server

[Guest]

I know you can create a temporary table in SQL Server 2000, but not quite sure how to do it in CFMX 7, i.e., does the SQL go inside a <CFQUERY dbtype="query"> tag?

I'm pulling the main set of records from an Oracle server (1st data source), but it does not contain employee names, only employee IDs.  Since I need to show the employee name along with the Emp ID, I'm then pulling a list of "current" employee names from a SQL Server (2nd data source), which is the main database on our CF server.

I've got a QofQ that works fine, except it only matches EmpIDs that exist in both result sets.  Employees who are no longer employed, don't match, and don't display.  Since I can't do a LEFT OUTER JOIN with a QofQ, what I need to do is get the records from the Oracle server into the SQL Server.  Preferably in a temporary table.

I was hoping if I could get those Oracle records written to a temp table on the main SQL Server, in same database as the Employee Name table, I could then write a normal <CFQUERY> that uses a LEFT OUTER JOIN.

I think I could probably write a Stored Procedure that would execute the SQL to create the temporary table, but am trying to avoid having to write the SP, and do it the simplest way.

This query will be a program that can be run hundreds of times per day, with a form that allows users to select date ranges, locations, and other options.  That starts the queries, which creates the report.  So I just need the temp table to exist only until all the SQL has run, and the <CFOUTPUT> has generated a report.

If the premise is right, I just need some help with the syntax for creating a SQL Server temp table, when you want to write records to it from an external data source.  I'm trying the following, but getting an error:

<CFQUERY name="ITE_Temp" datasource="SkynetSQL">
CREATE TABLE #MyTemp
(   INSERT INTO #MyTemp
ITE2.TrueFile char (7) NOT NULL,
ITE2.CountOfEmployee int NULL,
ITE2.DTL_SUBTOT decimal NULL,
ITE2.EMPTYPE char (3) NULL,
ITE2.ARPT_CD char (3) NULL
)
</CFQUERY>

So I actually created a permanent table on the SQL Server, and wrote the below SQL, which does work, and does write the records to table.  I can then write another CFQUERY with a LEFT OUTER JOIN, and get all the records, including those that don't have matching employee name:

<CFQUERY datasource="SkynetSQL">
<CFLOOP index="i" from="1" to = "#ITE2.RecordCount#">
INSERT INTO ITE_Temp
   (FullFile,
   EmployeeCount,
   DTL_Amount,
   EmployeeType,
   station)

VALUES  ('#ITE2.TrueFile#',
   #ITE2.CountOfEmployee#,
   #ITE2.DTL_SUBTOT#,
   '#ITE2.EMPTYPE#',
   '#ITE2.ARPT_CD#')
</CFLOOP>
</CFQUERY>

But, I hate to have to create a table and physically write to it.  For one, it seems slower, and doing it in temp would be in memory, and probably much faster, correct?  Is there some way to code the above, so that it does something similar, but in a TEMPORARY TABLE?   If I can figure out how to do this, I can pull data from multiple data sources and servers, and using SQL Server temp tables, work with the data as if it was all on the same SQL Server, and do some cool reports.

Everything I've done for the past few years, has all been from data from a single source, whether SQL Server, or another server.  Now I need to start writing reports where data can come from 3 or 4 different servers, and be able to do joins (inner and outer).  Thanks for any advice/help.  Much appreciated.

Gary

[Dan_Bracuk]

You can do a left join with Q of Q like this.

select q1.field1, q1field2, q2.field3

from q1, q2

where q1.field1 = q2.field1

union

select field1, 'constant', field3

from q2

where field1 not in (#valuelist(q1.field1)#)

[Guest]

While waiting to hear back, I was able to write the query results from an outside Oracle server, to a table on the local SQL Server, and do the LEFT OUTER JOIN required for the final query and report to work.  That was with this syntax:

<CFQUERY name="AddTableRecords" datasource="MyTable">
TRUNCATE TABLE ITE_Temp

<CFOUTPUT query="ITE2">
INSERT INTO ITE_Temp
(FullFile,EmployeeCount,DTL_Amount,EmployeeType,station)
VALUES
('#TrueFile#', #CountOfEmployee#, #DTL_SUBTOT#, '#EMPTYPE#', '#ARPT_CD#')
</CFOUTPUT>
</CFQUERY>

However, I was not able to write to a temporary table AND read the results. I got the syntax to run to write the above results to a temporary table.  But when I tried to read and output the results from the temp table, I got an error.  Also, it wouldn't take the single "#" (local) only the global "##" table var, using this syntax.  Note that if I didn't have the DROP TABLE in the beginning, the 2nd time you run this query, you get an error telling you the table already exists.

<CFQUERY name="ITE_Temp2" datasource="MyTable">
DROP TABLE ##MyTemp2
CREATE TABLE ##MyTemp2
(
FullFile char (7) NOT NULL,
EmployeeCount int NULL,
DTL_Amount decimal NULL,
EmployeeType char (3) NULL,
station char (3) NULL
)
<CFOUTPUT query="ITE2">
INSERT INTO ##MyTemp2 VALUES
(  
'#ITE2.TrueFile#',
#ITE2.CountOfEmployee#,
#ITE2.DTL_SUBTOT#,
'#ITE2.EMPTYPE#',
'#ITE2.ARPT_CD#'
)
</CFOUTPUT>
</CFQUERY>

So even though the above works, I could use some help in reading/writing the output.  I've tried several things similar to below, but they don't work.  It't telling me ITE_Temp2 does not exist.  It's not easy to find good examples of creating temporary tables in SQL Server.

<CFQUERY name="QueryTest2" datasource="SkynetSQL">
SELECT *
FROM ITE_Temp2
</CFQUERY>

<CFOUTPUT query="ITE_Temp2">
Output from Temp Table<br>
<p>FullFile: #FullFile#, EmployeeCount: #EmployeeCount#</p>
</CFOUTPUT>

Thanks for any help/advice.

Gary.

[Dan_Bracuk]

When you run a Q of Q like this:

select *

from QueryName

QueryName has to be a select query.  In your case, it was a create table query.

Something to remember with temporary tables on web apps.  You have to have plan for mulitple users in rapid succession.  At work, we do it with a custom tag that takes the sql as an attribute, creates a table with a random table name (with 3 tries, just in case) as returns the name of the table.