How about using BCP?
<cfquery name="InsertInitial" DATASOURCE="#DSN#" USERNAME="#user#"
PASSWORD="#pass#">
BULK INSERT Table
FROM 'D:\FR\FileResource\101-200.csv'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR='
'
)
</cfquery>
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ts_ba-bz_4fec.htm
jon
Marc Garrett wrote:
>To the group:
>
>I need to walk through about 11,000 records with 25 columns in a text file and insert
>them daily into a SQL Server database. Since I'm on a shared server, my templates
>need to execute in 180 seconds or less.
>
>If I use CFQUERY to insert row by row into the database, the query takes about 160 to
>200 seconds to complete. In an effort to boost performance, I wrote a stored
>procedure in SQL Server and called it via CFSTOREDPROC from CF. But the sp takes
>about 183 to 185 seconds to execute.
>
>Is this expected? Am I better off trying a query that simply calls the sp instead of
>using CFSTOREDPROC?
>
>Thanks for any assistance.
>
>Regards,
>
>Marc Garrett
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
