By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,956 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,956 IT Pros & Developers. It's quick & easy.

BCP column insert question

P: n/a
HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:

Text file data:
1234 abc def ghi jkl mno

Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N

My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?

Appreciated any help.

Thanks,
Teresa
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Teresa,

Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.

Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.

Here is an example from the Transact SQL online:

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!

Barry

"TThai" <tp****@pepco.com> wrote in message
news:7f**************************@posting.google.c om...
HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:

Text file data:
1234 abc def ghi jkl mno

Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N

My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?

Appreciated any help.

Thanks,
Teresa

Jul 20 '05 #2

P: n/a
"Barry Young" <yo******@insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@attbi_s54>...
Teresa,

Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.

Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.

Here is an example from the Transact SQL online:

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!

Barry

"TThai" <tp****@pepco.com> wrote in message
news:7f**************************@posting.google.c om...
HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:

Text file data:
1234 abc def ghi jkl mno

Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N

My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?

Appreciated any help.

Thanks,
Teresa


Hi Barry,
Appreciated your response. I'll try it and keep you posted. Have a good day.

Thanks,
Teresa
Jul 20 '05 #3

P: n/a
"Barry Young" <yo******@insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@attbi_s54>...
Teresa,

Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.

Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.

Here is an example from the Transact SQL online:

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.

BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')

Thanks,
Teresa
Barry

"TThai" <tp****@pepco.com> wrote in message
news:7f**************************@posting.google.c om...
HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:

Text file data:
1234 abc def ghi jkl mno

Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N

My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?

Appreciated any help.

Thanks,
Teresa

Jul 20 '05 #4

P: n/a
TThai (tp****@pepco.com) writes:
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.

BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')


BULK INSERT operates on the server, so it is looking a C:\ at your server.
If your file is on a client machine, you are better off with BCP. You
can specify field terminator with the -t options. Since | is a meta-
character for the command shell, you need to quote it:

bcp db..tbl in yourfile.txt -c -t "|" -S ....

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...
TThai (tp****@pepco.com) writes:
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.

BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')


BULK INSERT operates on the server, so it is looking a C:\ at your server.
If your file is on a client machine, you are better off with BCP. You
can specify field terminator with the -t options. Since | is a meta-
character for the command shell, you need to quote it:

bcp db..tbl in yourfile.txt -c -t "|" -S ....


Thank you very much. It worked!

Teresa
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.