akej via SQLMonster.com (fo***@SQLMonster.com) writes:
BULK INSERT myTable FROM 'E:\temp\slask.bcp'
Is it possible to do mapping
e.g
in my slask.bcp file 20 columns, i need to insert to myTable that have
suppose 15 columns also the names of colums in the file and table are
DIFFERENT, so how to acomplish mapping ???
Yes, this is possible. The format file describes in input file, and
is indeed a mapping to the table. Each line describes a field in the
file, and possibly also maps it to a table column. Here is a format file
with for a file with three fields:
8.0
3
1 SQLCHAR 1 0 "" 7 col1 Hungarian_CS_AI
2 SQLCHAR 0 10 "" 2 col2 Hungarian CS_AI
3 SQLCHAR 0 0 "\r\n" 5 col5 ""
The first number is the field number, and they should come in consecutive
order. Note that the 3 on the second line, describes the number of fields
in the field.
The next is the data type *in the file*. As long as you work with text
files this is always SQLCHAR (or SQLNCHAR for Unicode files), no matter
the data type of the target column in the database. But it is possible
to bulk load binary files, for which you would use SQLINT and such.
The next three fields describes the field is in the file. And while
they can be combined, you normally use them one by one. (Honestly, I
don't exactly what happens if you combine them.)
The first of these columns is a "prefix length" and is always 1, 2 or 4.
This prefix gives the actual length the of the field, and the prefix
is itself a binary value of 1, 2 or 4 bytes. From this follows that
prefix lengths are rarely used with data files in text format.
The sceond of these columns is a fixed length in bytes.
The last column is a character sequence that terminates the field. Note
that in this example the delimiter is newline, \r\n, and many files do
indeed have one data record per line in the file. However, BCP makes
no such assumptions, and will just iterate over the field definitions,
and if a newline appears in what BCP thinks is in the middle of a text
field, then that newline is handled as data. This permits you to import
data with newlines, but it also means that when BCP goes out of sync,
it gets out of sync badly.
Next number is the mapping you are looking for. This is the column
number in the database. In the example the file loads columns 2, 5
and 7 in the database. You can also specify 0 to indicate that that
field in the table should not be loaded at all. This can be used to
handle formats like:
"quoted data","more quoted data",989
Next column in the format file is the column name, but the value of
this field is ignored, so you can put "" if you like.
The last column is the collation of the field in the text file, in
case you would need some conversion. You can set "" for non-character
columns, or set "" all the way to get some default.
All of what I have said here is Books Online, under
Administering SQL Server
Importing and Exporting Data
Using bcp and BULK INSERT
Using Format files
But my presentation above maybe somewhat clearer than the sections in
Books Online.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
