Post reply

How to “step through” a list of values creating a record for each value

  • February 16, 2017 at 7:42 am

    #1928849

    mpsqlsercen - Thursday, February 16, 2017 7:15 AM

    Hi,

    Yes, this is just a one time deal that does not need to be repeated.

    Thanks,

    MP

    I do this a lot for one time imports. ColA has the data I want imported. In ColB write a concatenate formula and plug your ColA into an insert statement. Then copy the formula down to all the row. Copy all the rows, paste into SSMS and run. Here's an example of a formula I used to update the discount level from a spreadsheet that a customer gave me with the information I needed:

    =CONCATENATE("UPDATE Customer SET DiscountLevel = '",+B1,"' WHERE CustomerID = '",+A1,"';")

    I just copied that formula down the spreadsheet, copied and pasted all those updates into SSMS and I was done.

  • February 25, 2017 at 2:11 pm

    #1930343

    mpsqlsercen - Thursday, February 16, 2017 7:41 AM

    Jeff Moden - Wednesday, February 15, 2017 10:03 PM

    Why not just load the ACE drivers and read directly from the spreadsheet?

    Jeff,
    Sorry, I'm not even sure what ACE is actually.  I have done a couple SSIS things before  but I was looking for a quick fix as I'm not a pro at SSIS and it would take me along time to get it rolled out.

    I appreciate the idea however!
    Thanks!

    You can get a copy of the ACE drivers here. http://www.microsoft.com/en-us/download/details.aspx?id=13255
    They'll like the old "JET" drivers.  They allow you to do things like read and write from and to spreadsheets and a couple of other things.
    Robert Sheldon has a great introduction to them here. https://www.simple-talk.com/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/

    The Website you downloaded from has a set of instructions that will work but ONLY if there are no 32 bit applications on your machine.  DO NOT UNLOAD YOUR APPLICATIONS!  IT’S NOT NECESSARY!!!  Instead, find where you downloaded the AccessDatabaseEngine_x64.exe file to, open a DOS window, change to that directory, type the following command, and press enter.
          AccessDatabaseEngine_x64.exe /quiet
    The command above will install the driver/provider whether or not any 32 bit programs exist on the server.
    Be sure to allow any changes when prompted.  You should have administrator privs on the box to do this installation.
    You’ll need to restart SSMS (not the server) for the changes to take effect.

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

Viewing 2 posts - 16 through 16 (of 16 total)

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