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>