How do you specify field lengths with the Bulk Insert command?
Example: If I had a table named c:\Temp\TableA.txt and it had:
123ABC
456DEF
And I had a table such as:
use tempdb
CREATE TABLE TABLEA(
Field1 char(3),
Field2 char(3)
)
BULK INSERT TableA FROM 'C:\Temp\TableA.txt'
SELECT * FROM TableA
Then how would I specify the lengths for Field1 and Field2?
I think you need to define a format file
e.g.
BULK INSERT TableA FROM 'C:\Temp\TableA.txt'
WITH (FORMATFILE = 'C:\Temp\Format.xml')
SELECT * FROM TableA
For that to work, though, you need a Format File, obviously.
See here for general info about creating one:
At a guess, from looking at the Schema, something like this might do it:
<?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="3"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="3"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLCHAR" LENGTH="3"/>
<COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLCHAR" LENGTH="3"/>
</ROW>
</BCPFORMAT>
You'd want to use a format file with your BULK INSERT. Something like:
9.0
2
1 SQLCHAR 0 03 "" 1 Field1 ""
2 SQLCHAR 0 03 "\r\n" 2 Field2 ""
