SQL Data in form of Date+Time bulk insert into one column 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: 

&lt;?xml version="1.0"?&gt;
&lt;BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
 &lt;RECORD&gt;
  &lt;FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"/&gt;
  &lt;FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LEN GTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
  &lt;FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/&gt;
 &lt;/RECORD&gt;
 &lt;ROW&gt;
  &lt;COLUMN SOURCE="1" NAME="TStamp" xsi:type="SQLDATETIME"/&gt;
  &lt;COLUMN SOURCE="2" NAME="var2" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="3" NAME="var3" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="4" NAME="var4" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="5" NAME="var5" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="6" NAME="var6" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="7" NAME="var7" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="8" NAME="var8" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="9" NAME="var9" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="10" NAME="var10" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="11" NAME="var11" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="12" NAME="var12" xsi:type="SQLVARYCHAR"/&gt;
  &lt;COLUMN SOURCE="13" NAME="var13" xsi:type="SQLVARYCHAR"/&gt;
 &lt;/ROW&gt;
&lt;/BCPFORMAT&gt;
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 &lt;target&gt; WITH (TABLOCKX)
    &lt;target_column_list&gt;
SELECT
    &lt;columns_and_expressions&gt;
FROM OPENROWSET
(
    BULK &lt;data_file&gt;,
    FORMATFILE = &lt;xml_format_file&gt;,
    ... &lt;other_options&gt;
) AS alias;


The key part is the &lt;columns_and_expressions&gt; 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.