从电子表格更新现有数据库值 [英] Update existing database values from spreadsheet

问题描述

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

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.

电子表格是这样的:

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

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

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

因此对于每一行(大约 2500 行)

Thus for each line (approx 2500 rows)

每列匹配OLD的数据库值，需要改成NEW

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

我目前的想法是以类似的方式来做:更新 Oracle 数据库表的 SQL 语句来自 Excel 电子表格

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 制定一个替换语句列表，尽管这感觉是一种非常复杂的处理问题的方法！

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

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

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?

推荐答案

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

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.

看起来像这样

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)

这篇关于从电子表格更新现有数据库值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！