SQL Server Tips

Using OPENROWSET To Bulk Insert A Fixed Width Text File

TRUNCATE TABLE MYTABLE --delete all the data in the destination table

INSERT INTO MYTABLE
   (Col1, Col2, Col3, Col4)
   SELECT t.[c1], t.[c2], t.[c3], CAST(t.[c4] as datetime) As dte
   FROM OPENROWSET (BULK 'C:PATHTOFILE.TXT',
   FORMATFILE='C:PATHTOFORMAT.XML',
   ERRORFILE='C:PATHTOERRORFILE.TXT'
   ) As t

FORMAT.XML:

<?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="CharFixed" LENGTH="11"/> 
                <FIELD ID="2" xsi:type="CharFixed" LENGTH="11"/> 
                <FIELD ID="3" xsi:type="CharFixed" LENGTH="11"/> 
                <FIELD ID="4" xsi:type="CharFixed" LENGTH="11"/>
                <FIELD ID="5" xsi:type="CharFixed" LENGTH="11"/> <!-- CR/LF hack -->
        </RECORD>
        <ROW>
                <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLCHAR"/>
                <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLCHAR"/>
                <COLUMN SOURCE="3" NAME="c3" xsi:type="SQLCHAR"/>
                <COLUMN SOURCE="4" NAME="c4" xsi:type="SQLCHAR"/>
                <COLUMN SOURCE="5" NAME="rn" xsi:type="SQLCHAR"/>
        </ROW>
</BCPFORMAT>

Sample XML Format Files