You cannot do a bcp in/out of selected columns from a flat file/table in to a table since bcp utility requires the number of coulmns and the datatypes in the flat file and the table to be same and in the correct sequence , just like an insert.
From the description of the problem ,the columns sequence specified in the flat file and in the table in ASE do not match each other as there is a column called date and also the sequence is not followed ,so you may have to change the sequence since data that needs to be inserted through bcp should match the table definition exactly.
The best way to insert the records is create a table say test1
CREATE TABLE Test1
CustomId varchar(30) NULL,
Account varchar(255) NULL,
Date varchar(10) NULL,
Amount varchar(255) NULL,
Book varchar(255) NULL
once you have created this table bcp in the data from the flat file . Make sure you have included the proper field terminator and row terminator ( -t , -r) parameters properly else the bcp in will not be successfull.
In my case , the flat file looks like this row terminator /r field terminator ,
2345T,33897,20011222,35.00,Java/3456S,437898,20011025,79.34,Programming in C++/
Ryan told you "since bcp utility requires the number of coulmns and the datatypes in the flat file and the table to be same and in the correct sequence, just like an insert."
This is not correct. When you're importing with BCP, you can skip columns in the input file, you can decide not to load anything into some columns in the table, and you can load columns in any order you want.
Look in the online help for SQL (called Books Online), look under BCP Utility, and double-click the item called Format Files. It will bring up a menu with choices called "Using a Data File with Fewer Fields", "Using a Data File with Fields in a Different Order", etc.
The documentation isn't 100% clear and understandable, but at last you should know what BCP can do.
("Just like an insert" -- even an insert doesn't require the fields to be in the same order -- Insert allows you to specify the field list, which means the fields can be in any order you want.)
Maybe I did not read the documentation for BCP clearly but , when I executed the bcp command it did not give me the option for specifing individual column names , this is taking into consideration that I have no
format file .
Also in an insert into a table how can you skip the field if the column has the property not NULL or does not have a default bound to it .
Walker do you have a working example of the bcp in with selected columns if , yes please post , coz it could be very useful for all of us.