BCP Utility - omit default values in import file

Question

I have a table:

CREATE TABLE [dbo].[EXTRACT](
    ID int IDENTITY,
    CREATE_DTTM datetime default GETDATE() NOT NULL,
    CONTRACT_ID varchar(30) NOT NULL,
    ACCOUNT_NUMBER varchar(30) NOT NULL,
    EXCEPTION_REASON varchar(max),
    PRIMARY KEY(ID ASC)
);

And a source file that only supplies columns 3, 4 and 5. Below is my XML format file:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="12"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="24"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="CREATE_DTTM" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="3" NAME="CONTRACT_ID" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="ACCOUNT_NUMBER" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="EXCEPTION_REASON" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

which works with this input file:

,,contract_2,account_2,exception_reason_2

But is it possible to omit the first two fields in the input file to make it like this:

contract_2,account_2,exception_reason_2

Tab Alleman · Answer 1 · 2018-08-21T18:33:47.553

From the docs, remove the column rows that you don't want to populate from your format file, and renumber the fields and sources accordingly.

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="CONTRACT_ID" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="ACCOUNT_NUMBER" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="EXCEPTION_REASON" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

That being said, the safest, cleanest way to do this is to Bulk Import your data into a staging table that is shaped the same as your source file, and then call a procedure that moves the data from your staging table to the final destination table.

I have deduced the same from the docs yet it does not work. If I adjust the format file as you have described, I get "Invalid date format" error. I don't want to use another table shaped like my extract since the target table [EXTRACT] is already a staging table from which rows will be processed and pushed into a "historical" table along with processing result. — Igor, Aug 21 '18 at 20:48

BCP Utility - omit default values in import file

1 Answers1