1

Background of problem: To insert 2 seperated fields of data (from a big flat file document of data) with bulk insert. Columns in question that i want to get data from is in its current form in date time format (in flat file document).

I want to: insert these 2 fields of data into one column.

Idea of solution: Is it possible by xml format file point the fields of data into one column?

Example code (One line of data):

2016-01-01  00:00:00            GTM         clocktic                        

Into column:

   [TStamp] [datetime]     NOT NULL,

With 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="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LEN GTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="TStamp" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="2" NAME="var2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="var3" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="var4" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="var5" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="var6" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="var7" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="var8" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="var9" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="var10" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="var11" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="12" NAME="var12" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="13" NAME="var13" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>
  • Can you not load them separately then concatenate them into a third field post-load? – Mark Sinkinson Mar 3 '16 at 16:53
  • That would just sugarcoat my problem from my perspective, I am really interested if i cant define them as 2 fields in the format file and point them into one column. I am just not sure of the process – Froxer Mar 3 '16 at 16:55
2

You can process the data using regular T-SQL as part of the bulk loading process, using the OPENROWSET(BULK... function and the XML format file.

INSERT <target> WITH (TABLOCKX)
    <target_column_list>
SELECT
    <columns_and_expressions>
FROM OPENROWSET
(
    BULK <data_file>,
    FORMATFILE = <xml_format_file>,
    ... <other_options>
) AS alias;

The key part is the <columns_and_expressions> in the select clause. This provides a lot of flexibility.

A more concrete example:

INSERT dbo.zz_u_test_TEMP WITH (TABLOCKX)
(
    col1_dirty, col2_dirty, col3_dirty, col4_dirty, col5_dirty,
    col6_dirty, col7_dirty, col8_dirty, col9_dirty, col10_dirty
)
SELECT
    REPLACE(RowSource.col1_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col2_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col3_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col4_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col5_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col6_dirty, CHAR(34), SPACE(0)),
    RowSource.col7_dirty,
    RowSource.col8_dirty,
    RowSource.col9_dirty,
    RowSource.col10_dirty
FROM OPENROWSET
(
    BULK 'C:\Documents and Settings\Paul\Test.data', 
    FORMATFILE = 'C:\Documents and Settings\Paul\format.xml',
    CODEPAGE = 'RAW',
    FIRSTROW = 0,
    LASTROW = 0,
    MAXERRORS = 0,
    ROWS_PER_BATCH = 0
) AS RowSource;

This gives you the potential benefits of minimally-logged fast bulk load, while applying transformations in the SELECT clause.

That is a code example I wrote years ago for a different problem, but it should work for you. If I get a moment to create a table and file (not supplied in the question), I might add a more specific version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.