July 28, 2012 at 4:53 pm
Hi,
I have this task I need to complete. I need to execute it via a script in coldfusion, even though I know the BULK INSERT is the best way to go. But I am not versed on this and have limited time.
Scenario: the client exports a large text file with around 7500 lines. I upload that file, read it, parse it and then enter it into the database. I worked through several errors, including the TDS error for having too many parameters and finally arrived at a statement that works fine, up till I tested it on the larger exported text file and hit the latest error - this being the 1000 row insert limit.
I read that there is a way to use insert select union all to overcome this. Is this so? I am using coldfusion and SQL Server 9.
Here is my statement as it is now (sorry to all the really good coders here... I know this aint purty)
<cfquery name="multiInsert" datasource="#APPLICATION.DSN#">
INSERT INTO dbo.classifiedads (issueDate, catID, classifiedAd)
VALUES
<cfloop list="#camsdata#" index="FileItem" delimiters="|">
<cfset var_iteration = var_iteration + 1>
(
'<cfoutput>#issuedate#</cfoutput>',
'<cfoutput>#ListFirst(FileItem, chr(09))#</cfoutput>',
'<cfoutput>#ListLast(FileItem, chr(09))#</cfoutput>'
)
<cfif (var_iteration NEQ var_counter)>,</cfif>
</cfloop>
</cfquery>
Can anyone advise me. Is what I am trying to do possible? Can I get past this 1000 row limit by modifying my SQL statement?
Thank you,
TC
July 28, 2012 at 5:38 pm
I continued messing with it and seem to have gotten this fixed. The updated code is below, just in case it may help others:
<cfquery name="multiInsert" datasource="#APPLICATION.DSN#">
<cfloop list="#camsdata#" index="FileItem" delimiters="|">
INSERT INTO dbo.classifiedads (issueDate, catID, classifiedAd, email)
VALUES
(
'<cfoutput>#issuedate#</cfoutput>',
'<cfoutput>#ListFirst(FileItem, chr(09))#</cfoutput>',
'<cfoutput>#ListLast(FileItem, chr(09))#</cfoutput>',
'noemail'
)
</cfloop>
</cfquery>
TC
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply