BCP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BCP

Author

Topic

Nith
Starting Member

17 Posts

Posted - 2010-06-04 : 03:58:06

Dear buddies,

Not sure whats the error I am receiving with this bcp command.

select ('bcp
LOAD DATA INFILE C:\migration\datfile\bcp\' + tablename + '.txt' +
' BADFILE C:\Users\11636\Desktop\migration\' + tablename + '.bad' +
' DISCARDFILE C:\Users\11636\Desktop\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.

Nith

Nith
Starting Member

17 Posts

Posted - 2010-06-04 : 05:48:09

C:\Users\>bcp
"LOAD DATA INFILE C:\migration\datfile\bcp\CRS_AddDropDtl.txt
BADFILE C:\migration\CRS_AddDropDtl.bad
DISCARDFILE C:\migration\discard\CRS_Ad dDropDtl.dsc
INSERT INTO TABLE CRS_AddDropDtl FIELDS TERMINATED BY , OPTIONALLY
ENCLOSED BY "" TRAILING NULLCOLS( Task_ID, StudentNo, Progm_No,
SemesterCode, SemesterEnrol, Sub_Code, SYear, SMonth, Type,
Apv_Status, Rej_Status, AutoReject, AddedDT) "
out d:\bcp\CRS_AddDropDtl.txt -c -U username -P password
-S server_name;

An error occurred while processing the command line.

Any idea how I can over come this? ( I aligned the commands in new lins just for reading convinience, when I execute them, its in the same line.

Thanks

Nith
Starting Member

17 Posts

Posted - 2010-06-06 : 23:57:22

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.

Nith
Starting Member

17 Posts

Posted - 2010-06-07 : 03:20:47

Hi everyone!

Could someone help me with this. Its working fine but I just need it to break into different files.

select ('bcp "select selection from MASTER.dbo.Temp_TableLists" queryout d:\bcp\' + TableName + '.txt -c -U CRS -P CRS -S sqlserver09.ucsi.edu.my;')
from temp_tablelists

temp_tablelists has 3 columns: table_name: contains the desired tablenames
columnname: columns in the table
selection: the text with which I need the respective tablename and column names

When I run this bcp, I get the commands in a single file. How can I get them in different files as per the table name?

Please guide me.

Nith
Starting Member

17 Posts

Posted - 2010-06-07 : 21:39:03

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources