OutOfMemory error for Insert statement

[Derek_Guo]

I am trying to execute a insert statement like "Insert into B (c1,c2...) select c1,c2.... from B" by using <cfquery>, but a OutOfMemory error pop up after a long time runing.

I am using Teredata DB,  Is there anybody know what is the root cause? and how can i fix this.

Thanks for ur help.

[Dan_Bracuk]

What happens when you try to run this query without using Coldfusion?

[Derek_Guo]

It works fine when in Teredata SQL Assistant. One thing to mention is there are around 7,500,000 records will be processed.

When I run it with cfquery, the error msg came out.

[Owainnorth]

Almost certainly you'll be better off doing a command like that from with the database engine, using a Stored Proc or similar. Then you only need to use CF to call the proc.

[Derek_Guo]

Ye, u right. the procedure is definitely the best solution.

But actually we are not allowed to use procedure in that DB because of some policy concern..

I tried again, now the problem is, the insert statement actually has successfully finished (A table has been fully populated), but the cfquery is still running,  the other codes after </cfquery> didn't get the chance to run, any thought on this?

Thanks!!!

[Reed_Powell-ttnmOb]

I've never worked with Teradata, only MS SQL Server, but one thought is that Teradata, or the ODBC driver that CF is using, must be sending back data even though you would think there is no need for it to.  The number of records that the SELECT picks up shouldn't matter since they are being passed to the INSERT statement within the Teradata server itself.  To start debugging this I would change the SELECT so that it only picks up 100 or so records (ie, enough to be meaningful but not enough to crash/hang your app), and then do a CFDUMP of the query result when the CFQUERY completes.  What is it sending back?  If you don't get a lot of meaningful info, try adding RESULT="X" to the CFQUERY and then CFDUMP VAR="#X#" after the CFQUERY and see what it says is going on.

-reed

[Derek_Guo]

Sorry for the typo.

The SQL should be:

Insert into A (c1,c2...) select c1,c2.... from B

[Dan_Bracuk]

Is there any reason you have to insert into A instead of simply using B?