Home Forums SQL Server 2012 SQL Server 2012 - T-SQL OPENROWSET - how to read everything as text RE: OPENROWSET - how to read everything as text

  • January 29, 2016 at 6:08 pm

    #1855429

    Ed Wagner (1/29/2016)

    Once you get the syntax of the format file down, the BULK INSERT command is a rock-solid approach. You get to control the data type and maximum length of each and every column, the delimiters and everything else. You can write the whole thing in a stored procedure and the format file controls how the data is brought you into the staging table. I've used it for years and it's very reliable.

    The format file is covered at https://msdn.microsoft.com/en-us/library/ms190393.aspx.

    I'll also add that it will also allow you to sequester bad rows in a file for troubleshooting without it blowing the whole batch off, if you need something like that.

    --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)