bcp error

Question

bcp error

kasi.alagu

SSC Enthusiast

Points: 118
More actions
June 4, 2010 at 2:29 am

#233328

Dear buddies,
Not sure on how to construct this bcp statement.
select ('bcp
LOAD DATA INFILE C:\migration\datfile\bcp\' + tablename + '.txt' +
' BADFILE C:\migration\' + tablename + '.bad' +
' DISCARDFILE C:\migration\discard\' + tablename + .dsc
INSERT INTO TABLE ' + tablename + ' FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY "" TRAILING
NULLCOLS' + '(' + columnnames +')'
queryout d:\bcp\ '+ tablename + '.txt
-c -U username -P password -S servername;')
FROM Temp_Tablelists
Error: Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'queryout'.
Msg 105, Level 15, State 1, Line 8
Unclosed quotation mark after the character string ')
FROM Temp_Tablelists
'.
Temp Tablelists is a table with 2 columns, table names and the respective table's columns in delimited form.
I need bcp to create some text with the fields (columnnames and tablename in between).
Please guide me. I need one file per table.
Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

I have no idea what you are trying to do, but the error you have is not in your bcp statement but in SELECT itself and it is due to missing quotes and expression in few places:

1. Line 4 - singe quote is missing before ".dsc"

2. Line 6 - "+" is missing

3. Line 7 - singe quote is missing before "queryout"

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

kasi.alagu SSC Enthusiast Points: 118 More actions · Answer 2

Hi buddies,

Thanks for your reply.

This sql statement works fine. I have tested it.

select 'LOAD DATA INFILE ''D:\load\dat\' + TableName + '.dat''' +

' BADFILE ''D:\load\bad\' + TableName + '.bad''' +

' DISCARDFILE ''D:\load\dat\discard\' + TableName + '.dsc''' +

' INSERT INTO TABLE ' + TableName +

' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY "" TRAILING NULLCOLS' +

'(' + ColumnNames +')'

FROM Temp_TableLists

I need to create this LOAD DATA statement for each and every table(rows that exists in the table), so I want to use bcp.

So, I am adding bcp to this SQL statement.

bcp "select 'LOAD DATA INFILE ''D:\load\dat\' + TableName + '.dat''' + ' BADFILE ''D:\load\bad\' + TableName + '.bad''' + ' DISCARDFILE ''D:\load\dat\discard\' + TableName + '.dsc''' + ' INSERT INTO TABLE ' + TableName + ' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY "" TRAILING NULLCOLS' + '(' + ColumnNames +')' FROM Temp_TableLists" queryout d:\bcp\' + tablename + '.txt -c -U abc-P abc. -S abc;

Error: Unknown argument '+' on command line.

Where am I making the mistake. How can I use bcp in this case?

Thanks in advance.

Please guide me.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

kasi.alagu (6/6/2010)
...
Error: Unknown argument '+' on command line.
...
Please guide me.

This error is due to the fact that you missing some quotes in the select statement! Somewhere in your select the plus sign is taken into string instead of causing aggregation.

Does you SELECT query runs? If so please provide output.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

kasi.alagu SSC Enthusiast Points: 118 More actions · Answer 4

Hi!

Thanks for your reply.

Yes my select query runs.

This is my select query

select 'LOAD DATA INFILE ''D:\load\dat\' + TableName + '.dat''' +

' BADFILE ''D:\load\bad\' + TableName + '.bad''' +

' DISCARDFILE ''D:\load\dat\discard\' + TableName + '.dsc''' +

' INSERT INTO TABLE ' + TableName +

' FIELDS TERMINATED BY '','' OPTIONALLY ENCLOSED BY "" TRAILING NULLCOLS' +

'(' + ColumnNames +')'

FROM Temp_TableLists

and here is my output

LOAD DATA INFILE 'D:\load\dat\<tablename>.dat' BADFILE 'D:\load\bad\<tablename>.bad' DISCARDFILE 'D:\load\dat\discard\<tablename>.dsc' INSERT INTO TABLE <tablename> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "" TRAILING NULLCOLS( <columnname> )

I need this statement to be created in a file for each record in my table respectively.

Thanks once again.

kasi.alagu SSC Enthusiast Points: 118 More actions · Answer 5

Hi everyone,

Managed to solve it. Added a column to store the string I need and used it in bcp.

Thanks for all of your support.

Cheers!