Creating Temp SQL tables in CF

[countdrak]

I have a list which has an Item and Quantity pairs.

So it looks like this Item1:Quantity1,Item2:Quantity2....

11000:45,234000:21,34500:21.

Also I have a table with say two columns. ItemNum and Qauntity. I already have the item numbers inserted in this table. So 11000, 234000, 34500 are already in the table. Now I need to update the Quantity and maintain the data integrity. So I am tryin to join over the itemNum and I could do that if I create a temp table in CF. Query of queries is NOT a solution because as far as i know it doesnt support JOINS.

Also I dont wanna make multiple updates or inserts in a loop, coz I could have a list with a 10,000 items and quatities and that would mean 10,000 calls to the server. Also a join will assure that the right quantity is associated with the right itemNUM

Is there a way to create a temp table in coldfusion that I can do JOINS on ? Query of Queries in CF doesn't support inner or outer joins, so it quite pointless converting this list into a query object in CF. Any help will be greatly appreciated.

 

[FALCONSEYE]

you can create a temp table as follows

Create Table #tblTemp (someId int, someValue int null)

then you can insert into this table

insert into #tblTemp
values (@CurrentRec, @RecNum )


would this help?

 

[countdrak]

Can do this in a cfquery tag?

 

[r937]

countdrak, where does this list of 10,000 items come from?

yes, QofQ allows you to use a join, it just doesn't support the INNER JOIN syntax,

see Query of Queries

i'm not sure but couldn't you use QueryNew and QueryAddRow to feed your items into one of the table for the QofQ?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

rudy, i think what he's trying to avoid is looping and making 10,000 calls to the db. isn't there anyway to put that loop inside the one cfquery so it inserts all rows at once? I'm curious for an app i'm working on also.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[r937]

sorry, got sidetracked on the QofQ issue, and i see now that all that's necessary is to pass the data over to the database

if it's mysql, no problem --

insert into daTable (ItemNum, Quantity)
values
(11000,45)
,(234000,21)
,(34500,21)

if the database doesn't support that syntax, it might be trickier --

insert into daTable (ItemNum, Quantity)
values
(11000,45)
insert into daTable (ItemNum, Quantity)
values
(234000,21)
insert into daTable (ItemNum, Quantity)
values
(34500,21)

this depends on being able to pass more than one statement into the database in one CFQUERY tag

test it and see





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

Rudy,
thanks for the reply.
I'm using MS Access to store a few thousand records from an oracle db that i can't write to. (read only)

I tried your second solution

 <cfquery datasource = "CP30BS">
	INSERT INTO	testTable 	(a,b,c)
					values	('1','2','3');
	INSERT INTO	testTable (a,b,c)
					values	('4','5','6');
	INSERT INTO	testTable (a,b,c)
					values	('7','8','9');
	INSERT INTO	testTable (a,b,c)
					values	('10','11','12');												
</cfquery>

with and without the ; after each statement. neither worked.

I also tried the mysql syntax just for giggles. Turns out it wasn't very funny. I got the error i expected so that's a no go too.

<cfquery datasource = "CP30BS">
	INSERT INTO	testTable 	(a,b,c)
					VALUES	('1','2','3'),
							('4','5','6'),
							('7','8','9'),
							('10','11','12')
</cfquery>

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[countdrak]

My bad, I always assumed that QoQ doesnt support JOINS at all!

First of all the list of ITem:Quantity with 10,000 values comes from a VB DLL.I use cfobject to connect to it and send me back the list. I think what I am gonna do is parse this list and insert it in a Query object in coldfusion and see if the join works! *keep my fingers crossed*

I am not sure how slow this would be...but I will let you guys know.

Thanks a lot guys.

 

[r937]

yeah, the QofQ join will work, but do you not need to store something into a database?

if you're just reporting, QofQ is fine

truth, access can't handle more than one sql statement at a time

sad, eh

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[TruthInSatire]

yeah, oh well, i'll work something out, thanks for the info though.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[countdrak]

Ok QoQ is quite useless! I cant really do a whole lot with it. It would work great if somehow I could join a QoQ with a exsisting SQL table.

Now I build a Query object in CF but I cant really join it to my exsisting SQL table, and thats what I have been trying to do all this while.

So if I build a query object say qryItems using QueryNew and QueryAddRow (which is again is a slow process, coz I am looping and parsing the list to build the Q object) I cant really do something like this,

<cfquery name="Query1" datasource="#Request.DSN#">
   SELECT x.* ,m.*
   FROM #qryItems# x, SqlTable m
   WHERE x.itemNo = m.ItemNo
</cfquery>

I so wish I could do something like that...That would be soooo cool!

 

[r937]

you can do something like that

1. create your QueryList using QueryNew,QueryAdd...

2. run the DBQuery to get the data out of the database

<cfquery name="DBQuery" datasource="#Request.DSN#">
SELECT *
FROM SqlTable
</cfquery>

3. run the QofQ --

<cfquery name="QofQ" dbtype="query">
SELECT x.* ,m.*
FROM QueryList x, DBQuery m
WHERE x.itemNo = m.ItemNo
</cfquery>



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)