Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: 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

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

    Code:
    "0243567"~"08/26/06"
    How would I go about in my bulk statement to remove the " from the first column in the row.

    Thanks

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Well 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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think this might do what you want (it trims the first character off a field value).
    Code:
    UPDATE MyTable
    SET MyField = SubString(MyField,2,Len(MyField))
    HTH ~George
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •