Bulk Insert Question

I have a simple table I have created to do a bulk insert with a SP. Everything works fine but when the sp runs, the first column in the rows are like

"0243567

My bulk insert

BULK INSERT [Mas500_app].[dbo].[Test] FROM 'C:\Documents and Settings\Chris\Desktop\testbulk1.txt' WITH (FIELDTERMINATOR = '"~"', ROWTERMINATOR = '"')

the testbulk.txt file

"0243567"~"08/26/06"

How would I go about in my bulk statement to remove the " from the first column in the row.

ThanksWell there is no such options in bulk statement to remove the " from "0243567

the trailing " is removed becuase you have specified it as the field terminator

however you have two options
1) Change the txt file to remove the " from the first row data
2) issue the follwoing update statement immediately after the bulk insert statement

update [Mas500_app].[dbo].[Test]
set column1 = replace( column1 , " , '' )

HOwever the above replace statement is helpful only if your data in column1 does not contain any " else you will have to go for more elaborate procedures|||I think this might do what you want (it trims the first character off a field value).

UPDATE MyTable
SET MyField = SubString(MyField,2,Len(MyField))

HTH ~Georgesql