Setting the column width of a column in a table created by logparser I'm trying to use LogParser for ETL. I am trying to import this file with the following command:


  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, Field2 AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable


It creates a table like so:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](255) NULL,
    [Zip] [int] NULL
);


If I change the command to 


  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT Field1 AS CountryCode, TO_STRING(Field2) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -createtable -cleartable


It creates the following table:
    CREATE TABLE [dbo].[ZipCodes](
        [CountryCode] varchar NULL,
        [Zip] varchar NULL
    );

So TO_STRING(Field2) AS Zip INTO ZipCodes creates [Zip] [varchar](255) NULL.

My goal however, is for the resulting table to be:

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [char](2) NOT NULL,
    [Zip] [char](5) NOT NULL
);


Both columns remain VARCHAR(255) NULL with a SUBSTR like so:


  SUBSTR(Field1, 0, 2) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip


How do I get LogParser to create the table with fixed length NOT NULL char columns?
Its possible to get somewhat close to this. First precreate the table

IF object_id('[LogparserTest].[dbo].[ZipCodes]') IS NOT NULL
BEGIN 
    DROP TABLE [dbo].[ZipCodes]
END

CREATE TABLE [dbo].[ZipCodes](
    [CountryCode] [varchar](3) NOT NULL,
    [Zip] [varchar](6) NOT NULL
);


Note that you have to use varchar columns one character wider than your input.

Now run this command, note the lack of -createtable, but you can keep that there if you desire. It does't recreate an existing table:


  "c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" "SELECT SUBSTR(Field1, 0, 1) AS CountryCode, SUBSTR(TO_STRING(Field2), 0, 5) AS Zip INTO ZipCodes  FROM 'c:\Users\jdearing\Downloads\us.txt'" -i:TSV -headerrow:OFF -o:SQL -server . -database LogParserTest -cleartable -transactionRowCount:0  -ignoreMinWarns:OFF


If you create the correct length columns as char or varchar you get the following error for every row:

An error occurred while uploading data to SQL table
  SQL State:     22001
  Native Error:  0
  Error Message: [Microsoft][ODBC SQL Server Driver]String data, right
  truncation


However, note that SELECT MAX(LEN([CountryCode])), MAX(LEN(ZIP)) FROM [LogparserTest].[dbo].[ZipCodes]; Returns 2 and 5. Tracing the bulk insert with extended events shows that things are being paramaterized as follows so I don't know what's going on:

(@Param000004 char(2),@Param000005 char(5))INSERT [LogparserTest].[dbo].ZipCodes VALUES(@Param000004,@Param000005) 

So at this point you still need to perform an ALTER TABLE ALTER COLUMN command to alter each column, so it doesn't actually buy you much.