Post reply

Reading flat files from TSQL

  • October 5, 2007 at 3:16 am

    #178017

    Hi,

    I need to access data present in csv/txt files, say, a list of names in TSQL code.

    The ideas that come to my mind are importing the contents to a temporary table either through bulk load or DTS and use it in the TSQL.

    I wanted to know if there is any other file access command in TSQL using which I can read the data in txt file and use to process further.

    Thanks in advance.

    Best regards

    Kiran

  • October 5, 2007 at 4:00 am

    #739444

    With openrowset() and openquery() you can access the file directly. See the BOL for details.

  • October 5, 2007 at 6:13 am

    #739483

    Thanks joachim

    I think these commands are the ones I was looking for.

    Best Regards

    Kiran

  • October 6, 2007 at 2:22 pm

    #739813

    My recommendation would be to import the data into a staging table using BULK INSERT. Nasty fast... then, you can add the necessary indexes for performance... can't do that with OPENROWSET or the like...

    --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)
    Intro to Tally Tables and Functions

  • October 6, 2007 at 4:09 pm

    #739822

    OPENROWSET though gives you the ability to filter in the same query. If you don't need that flexibility you should follow Jeff's advice.


    * Noel

  • October 13, 2007 at 4:46 pm

    #741814

    You really think filtering on an OPENROWSET would be faster than just importing the table?

    --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)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply