CFEXECUTE and BCP

[WenAhu]

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>

[BKBK]

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?

[WenAhu]

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.

[BKBK]

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>

[WenAhu]

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]

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.

[WenAhu]

Yes, I looked at the link you gave me but when I use select into, I usually have 2 tables in my DB already.

In the past, I used select into statement when I need to copy content from table1 to table2 without cfloop record by record.

So what I did, I create table2 that is a mirror to table1 than used selct into statement within <cfquery> tags

So you are saying that the following sql statement will automatically create title2 table from title table? without Create table statement?

<!--- 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>

[BKBK]

So you are saying that the following sql statement will automatically create title2 table from title table? without Create table statement?

Yes. It may not work in MySQL, but I think it does in Sybase.

[WenAhu]

I got error that says CF can't work with external app on bcp

I may have to installed Sybase driver on my web server?

[BKBK]

I got error that says CF can't work with external app on bcp

I may have to installed Sybase driver on my web server?

I don't understand what you mean. Your original post suggests you did get Coldfusion and bcp to work together.