Using BCP output with SQLLDR

Question

Using BCP output with SQLLDR

Christopher Wright

Ten Centuries

Points: 1219
More actions
July 22, 2008 at 1:10 pm

#189976

Hi everyone,
I have a question about using SQLLDR to load some data that I obtained by running BCP on a SQL Server 2000 SP4 database table. I have found a way to delimit the fields so that there's no need to surround text fields with quotes.
Unfortunately, some of the data in the SQL text fields has carriage returns. SQLLDR wants to treat each line in the text file as a new record. Ooops!!!!
There is a way to deal with this by inserting [highlight=#ffff11]continueif last != "|"[/highlight] after the infile argument in the controlfile, but I can't make it work.
Does anyone have any experience with loading text with SQLLDR that has carriage returns?
Thanks in advance!!!
-Chris

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 1

Try to use the DTS (Data Transformation Services) if you are working with SQL Server 2000 or SSIS (SQL Server Integration Services) if you have SQL Sever 2005 or above.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Christopher Wright Ten Centuries Points: 1219 More actions · Answer 2

Hi,

Thanks for the reply. DTS is actually what forced me to try BCP. The DTS job was taking over 11 hours to complete. That's unacceptable for me. This takes about 3 minutes. 🙂

I found the answer to my questions. It's all in how you set up the BCP and SQLLDR statements. This is a 3 step process.

1. Generate BCP statements and run them as a batch job.

2. Generate statements to create control files for every table and run them from a DOS prompt.

3. Generate SQLLDR statements and run them as a batch job.

Here are examples of the statements:

BCP

bcp myDatabase.dbo.myTable out c:\TestBCPtoSQLLDR\AppDoc.csv -c -t~MyBatchFieldDelimiter~ -r~rbrk~ -T

Control file contents

load data infile 'c:\TestBCPtoSQLLDR\myTable.csv' "str x'7E7262726B7E0D0A'" into table mySchema.myTable fields terminated by "~MyBatchFieldDelimiter~" trailing nullcols (intField,charField CHAR(8000),charField2 CHAR(8000),intField2) > myTable.ctl

SQLLDR

sqlldr myUser@myTNSReference/password control=c:\TestBCPtoSQLLDR\myTable.ctl

Good stuff!

-Chris