Update existing database values from spreadsheet

Question

I have an existing MSSQL database where the values in some columns need updating according to a spreadsheet which contains the mappings of old data and new data.

The spreadsheet is like this:

       | OLD DATA      | NEW DATA      |
RECORD | A | B | C | D | A | B | C | D |
1      |OLD|OLD|OLD|OLD|NEW|NEW|NEW|NEW|
2      |OLD|OLD|OLD|OLD|NEW|NEW|NEW|NEW|

Where ABCD are the column names, which relate to the database, and OLD / NEW relates to the data.

Thus for each line (approx 2500 rows)

The database values that match OLD in each column, need to be changed to NEW

My current thoughts are to do it in a similar way to this: SQL Statement that Updates an Oracle Database Table from an Excel Spreadsheet

Essentially getting Excel to formulate a list of replace statements, though this feels like a horribly convoluted way to deal with the problem!

Is there a way to have SQL cycle though each row of the spreadsheet, check all records for a=old, b=old2, c=old3, d=old4 and then replace those values with the appropriate a=new, b=new2, c=new3, d=new4?

Answer 1

You shouldn't need to loop through each row in the spreadsheet. You can use the OPENROWSET command, like in the answer you linked to, to load the spreadsheet data into a sort of temporary table. You can then run a regular UPDATE statement against that table.

It would look something like this

UPDATE YourTable
SET YourTable.A = ExcelTable.NewDataA,
    YourTable.B = ExcelTable.NewDataB,
    YourTable.C = ExcelTable.NewDataC,
    YourTable.D = ExcelTable.NewDataD
FROM YourTable
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
         'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;',
         'SELECT column1name, column2name, column3name, column4name
          FROM [worksheetname$]') AS ExcelTable
ON YourTable.ID = ExcelTable.ID
WHERE (YourTable.A = ExcelTable.OldDataA
  AND YourTable.B = ExcelTable.OldDataB
  AND YourTable.C = ExcelTable.OldDataC
  AND YourTable.D = ExcelTable.OldDataD)

Answer 2

Looks like Jeff got you the answer you needed, but for anyone looking to update a database from a Google Sheet, here's an alternative, using the SeekWell desktop app. For a version of this answer with screen shots, see this article.

1. Get the right rows and columns into the spreadsheet (looks like @sbozzie already had this)

Write a SQL statement that SELECT 's all the columns you want to be able update in your sheet. You can add filters as normal in the WHERE clause.

2. Select 'Sheets' the top of the app and open a Sheet. Then click the destination icon in the code cell and select "Sync with DB"

3. Add your table and primary key In the destination inputs, add your table name and the primary key for the table. Running the code cell will add a new Sheet with the selected data and your table name as the Sheet name. Please note that you must start your table in cell A1. It's a good idea to include an ORDER BY.

4. Add an action column Add a "seekwell_action" column to your Sheet with the action you'd like performed for each row. Possible actions are:

Update - updates all columns in the row (unique primary key required)

Insert - adds the row to your database (you need to include all columns required for your database)

Sync - An Update action will be taken every time the query runs, on a schedule (see "5. Set Schedule" below)

Complete - status after the schedule has run (see below) and the actions have been taken. The new data should now be in your database. Note that 'Sync' actions will never show complete, as they run every time the schedule runs. To stop a 'Sync' action, change it manually.

5. Set Schedule To execute the actions, select the clock icon at the top of the application, indicate the frequency and exact time, and click 'Save.' You can manage your schedule from the inserted 'RunSheet' (do not delete this sheet or you will need to reset your schedule) or from seekwell.io/profile. If you need to run the actions immediately, you can do so from /profile.

Gotchas

You need to start your table in cell A1.

Snowflake column names are case sensitive. Be sure to respect this when specifying the primary key, etc.

If your server is behind a firewall, you will need to whitelist SeekWell's static IP address to use scheduling. See more about whitelisting here.