Skip to main content
W
WenAhu
Known Participant
November 6, 2009
Question

CFEXECUTE and BCP

  • November 6, 2009
  • 2 replies
  • 1618 views

Hi All, I have the following code example but what I need is an INSERT from a text file to a table. The following example is the reverse of what I need.

Does anyone know how ?

example for a select:

<cfset destfilename ="E:\tempdata\" & dateformat(now(),"mm-dd-yyyy") & TimeFormat(now(), "-hh-mm-ss") & ".txt">

<cfset qryStr = "select * from pubs.dbo.titles">


<cfexecute name='bcp "#qrystr#" queryout "#destfilename#" -U UserName -P Password  -c'></cfexecute>

This topic has been closed for replies.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 7, 2009

When I compare your code with the example from the BCP documentation, I come up with:

<cfset qryStr = "select * into pubs.dbo.titles2 from pubs.dbo.titles where 1=2">
<cfexecute name='bcp "#qrystr#" in "#sourceFileName#" -U UserName -P Password -c'></cfexecute>

An alternative idea:

<!--- create new, empty table to write to --->

<cfquery datasource="myDSN" name="writeToTable">
select * into pubs.dbo.titles2
from pubs.dbo.titles
where 1=2
</cfquery>

<cfexecute name='bcp "pubs.dbo.titles2" in "#sourceFileName#" -U UserName -P Password -c'></cfexecute>

W
WenAhuAuthor
Known Participant
November 9, 2009

I only have 1 table to insert. In your example you have title2 and title tables in the select into statement, I don't get it

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 9, 2009
I only have 1 table to insert. In your example you have title2 and title tables in the select into statement, I don't get it

I wanted to give an example similar to Example C in the referenced link. Have you looked at the reference?

My query creates a second table, based on the first, but doesn't populate it. It is the bcp command that then populates it with data from the file.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 7, 2009

What is your version of Coldfusion? Then we know whether you have a spade or a tractor. Also. what do you intend to read from the file?

W
WenAhuAuthor
Known Participant
November 9, 2009

I'm using Coldfusion 8, Unix and Sybase

All I need is to read column values from text file and insert them into a table

Currently I'm using cfloop which takes forever to complete. In my other project I did similar process but I did bcp manually and it took only a second to populate the table with 20 thousands records.

In this project I can't do it manually, it is part of CF application.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices