Home Forums SQL Server 2008 T-SQL (SS2K8) An alternative to split for performance benefits? RE: An alternative to split for performance benefits?

  • Rob-350472 (12/7/2012)


    I'm actually looking at the bulk insert technique now - in terms of data it's very simple, an Excel file with an email address per row, saved as a CSV, that's literally all.

    The bulk insert I'm trying keeps appending them all into one column rather than multiple rows though which is a bit annoying!

    IF object_id('tempdb..#EmailAddresses') IS NOT NULL

    BEGIN

    DROP TABLE #EmailAddresses

    END

    CREATE TABLE #EmailAddresses(

    Email Varchar(max) NOT NULL

    ) ON [PRIMARY]

    BULK INSERT #EmailAddresses

    FROM 'C:\Program Files\Microsoft SQL Server\Book1.csv'

    WITH

    (

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = ''

    )

    And that just spits out email addresses with a space between e.g. x@x.com y@y.com....

    I tried with 0x0a as the rowterminator instead but it generated the same results, I need to play some more with this (I've only used it once before a v long time ago!)

    How many email addresses are there on each line of the CSV file? Also, have yhou looked at the file to make sure it has commas in it?

    Heh... Duh! That was in the very first paragraph of that post. Sorry.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)