BCP Format File

[Rathtap]

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?

[Erland Sommarskog]

Rathtap (am****@yahoo.com) writes:

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Rathtap]

This is the table:
CREATE TABLE [dbo].[CT_RATE_TEMP] (
[code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CODERATE] [smallmoney] NULL ,
[CODERATE2] [smallmoney] NULL ,
[CODERATE3] [smallmoney] NULL ,
[CODERATE4] [smallmoney] NULL
) ON [PRIMARY]
GO

And here is the import file:
36415 25.00
52759 24.50
52760 12.77
52775 24.03
74285 16.88
74436 7.84
80003 7.58
80004 8.00
80005 8.92
80006 8.94
80007 9.32
80008 9.66
80009 9.90
80010 9.90
80011 10.08
80012 10.29
Here I would like Col1 of the file to map to the Code field and Col2
to CodeRate. CODERATE2,CODERATE3 and CODERATE4 should be null.

On the other hand there may be the following file to import where all
fields match, then there is no problem:
52759 24.50 24.50 24.50
52760 12.77 12.77 12.77
52775 24.03 24.03 24.03
74285 16.88 16.88 16.88
74436 7.84 7.84 7.84
80003 7.58 7.58 7.58
80004 8.00 8.00 8.00
80005 8.92 8.92 8.92
80006 8.94 8.94 8.94
80007 9.32 9.32 9.32
80008 9.66 9.66 9.66
80009 9.90 9.90 9.90
80010 9.90 9.90 9.90
80011 10.08 10.08 10.08
80012 10.29 10.29 10.29
80016 12.05 12.05 12.05
80018 12.14 12.14 12.14
80019 12.61 12.61 12.61
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>...

Rathtap (am****@yahoo.com) writes:

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

[mgran]

try adding default values to the null fields