SQL 从电子表格更新现有数据库值

Update existing database values from spreadsheet

提问by sbozzie

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.

我有一个现有的 MSSQL 数据库，其中某些列中的值需要根据包含旧数据和新数据映射的电子表格进行更新。

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.

其中 ABCD 是与数据库相关的列名，而 OLD / NEW 与数据相关。

Thus for each line (approx 2500 rows)

因此对于每一行（大约 2500 行）

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

每列匹配OLD的数据库值，需要改成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

我目前的想法是以与此类似的方式执行此操作： 从 Excel 电子表格更新 Oracle 数据库表的 SQL 语句

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

本质上是让 Excel 制定一个替换语句列表，尽管这感觉是一种非常复杂的处理问题的方法！

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?

有没有办法让 SQL 循环通过电子表格的每一行，检查 a=old、b=old2、c=old3、d=old4 的所有记录，然后用适当的 a=new、b=new2 替换这些值， c=new3，d=new4？

回答by Jeff Rosenberg

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

您不需要遍历电子表格中的每一行。您可以OPENROWSET像链接到的答案一样使用该命令将电子表格数据加载到某种临时表中。然后，您可以UPDATE对该表运行常规语句。

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)

回答by Thabo

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.

看起来 Jeff 为您提供了所需的答案，但对于希望从 Google Sheet 更新数据库的任何人，这里有一个替代方案，使用SeekWell 桌面应用程序。有关此答案的屏幕截图版本，请参阅此文章。

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

1. 在电子表格中获取正确的行和列（看起来@sbozzie 已经有了这个）

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

编写一条 SQL 语句，其中SELECT包含您希望能够在工作表中更新的所有列。您可以在WHERE子句中正常添加过滤器。

1. 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"

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

3. 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:

2. 选择应用程序顶部的“工作表”并打开工作表。然后单击代码单元格中的目标图标并选择“与数据库同步”

3. 添加表和主键 在目标输入中，添加表名和表的主键。运行代码单元格将添加一个新的工作表，其中包含所选数据和您的表名作为工作表名称。请注意，您必须在单元格 A1 中开始您的表格。包含一个ORDER BY.

4. 添加操作列将“seekwell_action”列添加到您的工作表中，其中包含您希望对每一行执行的操作。可能的操作是：

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)

同步 - 每次查询运行时都会按计划执行更新操作（请参阅下面的“5. 设置计划”）

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.

完成 - 计划运行后的状态（见下文）并已采取行动。新数据现在应该在您的数据库中。请注意，“同步”操作永远不会显示完成，因为它们会在每次计划运行时运行。要停止“同步”操作，请手动更改。

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

5. 设置计划 要执行操作，请选择应用程序顶部的时钟图标，指明频率和确切时间，然后单击“保存”。您可以从插入的“RunSheet”（不要删除此表，否则您将需要重置您的日程安排）或从seekwell.io/profile 管理您的日程安排。如果您需要立即运行操作，您可以从 /profile 执行此操作。

Gotchas

陷阱

You need to start your table in cell A1.

您需要在单元格 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.

如果您的服务器在防火墙后面，您需要将 SeekWell 的静态 IP 地址列入白名单以使用调度。在此处查看有关白名单的更多信息。