Search code examples
sql-serverbulkinsertbcp

bcp: replacing a value in the data field with null for a column

Let's say I have a table 

CREATE TABLE [T1](
   [ID] [int] NOT NULL,
   [Level] [int] NULL,
   [N1] [int] NULL,
   [N2] [int] NULL)

and I have a file that consists of four delimited fields 

 1,2,3,4
 2,a,5,8
 3,X,11,12
 ...

The file is huge (tens or hundreds of millions of rows) I am importing it using bcp utility. Obviously, the second field is not an integer, but for my application it is not important. So I want to import it to this table, skipping the second field. I can not change file format, or the DB schema. I have the following bcp 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="\t"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="ID" xsi:type="SQLINT"/>
  <COLUMN SOURCE="2" NAME="Level" xsi:type="SQLINT"/>
  <COLUMN SOURCE="3" NAME="N1" xsi:type="SQLINT"/>
  <COLUMN SOURCE="4" NAME="N2" xsi:type="SQLINT"/>
 </ROW>
</BCPFORMAT>

The question is: how can I specify that instead of field 2 I want column 2 to be just NULL? Another words, how can I instruct the bcp to skip a column? If I just remove 

<COLUMN SOURCE="2" ...

than all columns will shift and column 2 is filled with data from the field 3, column 3 is filled with values from the field 4 and the column 4 is left empty. An alternative way would be to set this column some fixed value, 0 for example, for every row.

Solution

  • So, according to the documentation: if you want skip any column except for the last you need to create a view that exposes the subset of columns you want tp import into.

    The view should look like create view v1 as select id, n1, n2 from t1 and then you can remove the <COLUMN SOURCE="2" NAME="Level" xsi:type="SQLINT"/> from the format file and make the import using the view as target.

    If you want to have a default value set for the missing column you should add a default constraint for that column before you do the import.

    For references see: Using a Format File to Skip a Table Column and Using a Format File to Skip a Data Field