Mark, perfect, thank you!

Thank you

Yes that works perfect, thank you.

This is the best I was able to do.  Is there a better way?

DECLARE @ColumnName2 varchar(99)SELECT @ColumnName2 = COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'##tmp' AND ORDINAL_POSITION...

Yikes, disregard the question; made a simple mistake.

As mentioned, I have a 12,000,000 line txt file; for testing I used a text editor (Ultra Edit) to add a row number.  Obviously in a production situation would not...

Unfortunately there is no way with the current file to keep order.  When I did my testing before bulk insert I used Ultra Edit to add a row number.  That...

What you posted helped, I could not identify what was filling the field.  What I'd wanted to do was get rid trailing 0x00 bytes.  Looks like this will work.  Thanks!

So concat field will look lie this:
10, 20, 30
10, 0, 30
11, 21, 31
21, 31, 0

not like this:
102030
10030
112131
2131

Lynn, that is exactly what I was needing.  Thank you.

LinksUp, very close.  Taking what you did and modifying it a bit gets the desired result.  Thank you.