Post reply

Upate column in a table based on query

  • January 1, 2008 at 1:03 pm

    #185240

    I need to update a column in one table in the database based on values in an excel spread sheet. The excel spreadsheet contains the primary key of the database and a date/time column that needs to be updated based on the primary key.

    What is the best way to do this and avoid SSIS if possible.

    Thanks

  • January 1, 2008 at 1:17 pm

    #763872

    OpenRowSet is one fine way... please the following URL...

    http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

    That'll get you started... there are a couple of "finesse" tricks that we can talk about later on, but you need the basics mention in the URL first. There's 3 pages in the URL... I'd recommend studying and playing with all 3. Setup a couple of simple tests using their examples so you understand everything that's going on.

    Also, if you're not login in with SA privs, it can still be done with OpenRowSet, but we'll need to make a simple registry change and a permissions change on the Temp/TMP directory of your server.

    A bit of a pain to setup if not using as SA... but once setup, it's very well worth it.

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

  • January 1, 2008 at 1:18 pm

    #763873

    Also, if a fellow by the name of "Madhivanan" responds to this thread, he's got some very good advice on OpenRowSet...

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

  • January 1, 2008 at 1:26 pm

    #763874

    excellent, thank you for the advise, I will try that, I didn't want to create a temp table and then drop it, we will only be running this file a couple of times and wanted something simple and to the point

    Again thank you and have a Great 2008

  • January 2, 2008 at 6:18 am

    #764005

    Ok, call me a hack, but I have to do this a lot and I cheat.

    In a new column on the spreadsheet, enter a formula like this:

    ="UPDATE MyTable SET MyValue='" & B2 & "' WHERE MyID=" & A2

    Copy and paste the formula to every row in that column.

    When you copy the column and paste it into Query Analyzer or a Management Studio query, it will get the results of the formula in the copy buffer, not the formula.

    So, you will end up with a series of single row updates.

    It's a bit of a hack, but for single instances of updates it is really easy to do and takes a few seconds of writing T-SQL.

  • January 2, 2008 at 10:46 am

    #764119

    Ok, call me a hack, but I have to do this a lot and I cheat.

    No, you are neither a hack nor a cheater. You ARE making the best use of your time. Fast is better than slow, and writing code for one time use is seldom worth the time it takes.

    Having excel write the code, and pasting works; with larger datasets, I'd recommend microsoft access for a one time import.

  • January 2, 2008 at 11:44 am

    #764150

    I never said anything about being a hack or a cheat as being a bad thing.

    And I freely admit that I don't always make the best use of my time.

    Thanks for the support though.

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

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