Visual Studio SSDT 数据比较：如何比较同一数据库中的两个表

Question

尝试在SSDT中进行简单的数据比较，但证明有点困难。

在一个数据库中，我有两个表需要比较。

这些表具有相同的模式，只是表名不同。我只想看看这个工具是否能够提供一种很好的方式来比较两者之间的数据。

即：

tblOutput
tblOutput_210314

但是在同一数据库中选择两个表进行比较，我不知道如何实现。

似乎只能选择存在于源数据库和目标数据库中的表名。由于我的源数据��和目标数据库相同，我基本上是在将自己的表与自己进行比较？

是否有人知道如何使用数据比较来实现此目标？

• Visual Studio 2012
• SQL Server 数据工具
• 数据比较

- StevieB

Answer 1

数据比较仅支持比较具有匹配架构的两个不同数据库。很遗憾，您无法使用数据比较进行您期望的操作。根据帮助文档：

要求：当您比较表或视图中的数据时，源数据库中的表或视图必须与目标数据库中的某个表或视图共享几个属性。不满足以下标准的表和视图不会被比较，并且不会出现在“新数据比较向导”的第二页上：

- 表必须具有具有兼容数据类型的匹配列名。 - 表、视图和所有者的名称区分大小写。 - 表必须具有相同的主键、唯一索引或唯一限制。 - 视图必须具有相同的唯一聚集索引。 - 仅当表和视图具有相同名称时，您才可以将其进行比较。

- Kevin Cunnane

---

虽然我相信这是一个好的答案，因为它显示了Visual Studio数据比较工具的局限性，但它没有回答如何以其他方式实现。我有同样的问题-需要比较同一数据库中由我编写的不同版本视图创建的两个表（我通过SELECT * INTO tmpTable1 FROM myViewV1和 SELECT * INTO tmpTable2 FROM myViewV2来创建这些表，但由于数据比较工具的限制无法完成）。那么，有没有实际的方法可以做到这一点？（请注意，只有在稍后创建主键时才能使用SELECT * INTO。） - Matt

Answer 2

一个解决工具限制的方法。如果您有两个具有共同列需要进行比较的表格。一种解决方案需要复制数据库并比较视图。

例如，我们有一个名为CoffeeTableBooksDatabase的数据库，其中包含两个表table_myBooks和table_aFriendsBooks，这些表格有共同的列authors和titles需要进行比较。

请备份并还原数据库以创建副本：

1. 备份数据库（例如：CoffeeTableBooksDatabase）
2. 使用不同的名称还原数据库（例如：AnotherCoffeeTableBooksDatabase）

创建一个公共视图：

1. 在两个数据库中都创建一个相同名称的视图（例如：view_SomeBooks）
2. 在原始数据库（例如：CoffeeTableBooksDatabase）中，将第一个表（例如：table_myBooks）添加到视图（例如：view_SomeBooks）中，包括两个表共有的所有列（例如：authors和titles）
3. 在副本数据库（例如：AnotherCoffeeTableBooksDatabase）中，将第二个表（例如：table_aFriendsBooks）添加到视图（例如：view_SomeBooks）中，包括两个表共有的所有列（例如：authors和titles）

现在你可以通过查看视图比较来比较这两个数据库。虽然这不是一种推荐的解决方案，但使用该工具和其当前限制可以实现。

- Coding Fin

Answer 3

我这里是与SSDT数据比较有同样问题的人。你可以尝试使用 Spreadsheet Compare 2013程序，在Excel工作簿表格中比较这2个表格。

1）将SQL Server表中的数据转储到2个不同的Excel工作簿中，具有相同的工作表名称为“Sheet 1”。 2）运行Spreadsheet Compare程序，输入在步骤1中创建的2个工作簿的文件路径。 3）使用导出结果选项。差异将被导出到Excel中。 注意：每个字段的每个值更改都将成为“比较导出”中的新记录。 4）对于导出的结果（Excel），您可以添加一个带有表中关键字段的字段。我使用了INDIRECT公式并替换了范围值列字母（EXPL：“J7562”从“J”到“B”）。完整的公式如下所示。我需要为其他所需的关键字段多次执行此操作。 --数据集中的关键值，[PlanDimensionKey] (E2) ="'" & INDIRECT("'[JET upload LAST change.xlsx]dbo.OperatingPlan (import)'" & "!H" & RIGHT(B2,4)) & "'"

--数据集中的关键值，[Division] (F2) =INDIRECT("'[JET upload LAST change.xlsx]dbo.OperatingPlan (import)'" & "!C" & RIGHT(B2,4))

--数据集中的关键值，[PeriodKey] (G2) ="'" & INDIRECT("'[JET upload LAST change.xlsx]dbo.OperatingPlan (import)'" & "!I" & RIGHT(B2,4)) & "'"

--返回每个值差异的字段标签；在列序数位置输入 (H2) =CHOOSE(COLUMN(INDIRECT(LEFT(B11,1)&"1")), "ProductCode", "Concept", "DivisionKey", "PlanYear", "PlanVersion", "Year", "Period", "PlanDimensionKey", "PeriodKey", "NetSales", "Volume", "SalesCost", "GrossMargin")

5）然后，您可以使用文本编辑器（NotePad ++）将表格数据粘贴并编写SQL UPDATE语句。

• 在NOTEPAD++中用逗号替换制表符...将\t替换为,
• 为NOTEPADD++中的每个记录添加值括号...将\r\n替换为)\r\n,(

这不是我做过的最容易的事情，但它是一种可行的比较数据和编写SQL更改的方法。我能够使用这种方法相对简单地修改504个值。更改:= 7 SKU * 3列 * 12个月 * 2个部门 = 504个更改。很遗憾，VS SSDT无法比较具有模式差异的表。

--电子表格比较2013导出结果添加关键字段--

+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| A     | B                    | C                    | D                      | E                | F        | G         | H           |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| Range | Old Value            | New Value            | Description            | PlanDimensionKey | Division | PeriodKey | VALUE FIELD |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7562 | '1949.26'            | '105627.74'          | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-01' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7563 | '2023.39'            | '133624.49'          | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-02' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7564 | '2380.1799999999998' | '76481.8'            | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-03' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7565 | '2880.91'            | '76563.55'           | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-04' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7566 | '2797.08'            | '92416.79'           | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-05' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7567 | '2432.5700000000002' | '91324.19'           | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-06' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7568 | '3095.74'            | '100132.53'          | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-07' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7569 | '3613.07'            | '136506.1'           | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-08' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7570 | '2146.5700000000002' | '115062.45'          | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-09' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7571 | '2522.0500000000002' | '121164.3'           | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-10' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7572 | '2690.11'            | '135402.20000000001' | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-11' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7573 | '2180.79'            | '107210.08'          | Entered Value Changed. | '8351-US FOODS'  | 1        | '2019-12' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7574 | '1949.26'            | '105627.74'          | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-01' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7575 | '2023.39'            | '133624.49'          | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-02' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7576 | '2380.1799999999998' | '76481.8'            | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-03' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7577 | '2880.91'            | '76563.55'           | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-04' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7578 | '2797.08'            | '92416.79'           | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-05' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7579 | '2432.5700000000002' | '91324.19'           | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-06' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7580 | '3095.74'            | '100132.53'          | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-07' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7581 | '3613.07'            | '136506.1'           | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-08' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7582 | '2146.5700000000002' | '115062.45'          | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-09' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7583 | '2522.0500000000002' | '121164.3'           | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-10' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7584 | '2690.11'            | '135402.20000000001' | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-11' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| J7585 | '2180.79'            | '107210.08'          | Entered Value Changed. | '8351-US FOODS'  | 2        | '2019-12' | NetSales    |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7610 | '37095.74'           | '113500.3'           | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-01' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7611 | '57068.24'           | '150427.1'           | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-02' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7612 | '42449.3'            | '101808.75'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-03' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7613 | '42025.86'           | '110008.72'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-04' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7614 | '51727.27'           | '125514.74'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-05' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7615 | '45501.57'           | '106330.64'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-06' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7616 | '48026.35'           | '117350.16'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-07' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7617 | '51493.51'           | '148743.64000000001' | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-08' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7618 | '40102.230000000003' | '124767.09'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-09' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7619 | '40849.61'           | '114226.42'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-10' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7620 | '50533.83'           | '155022.29'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-11' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7621 | '42731.83'           | '122845.71'          | Entered Value Changed. | '8353-US FOODS'  | 1        | '2019-12' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7622 | '37095.74'           | '113500.3'           | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-01' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7623 | '57068.24'           | '150427.1'           | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-02' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7624 | '42449.3'            | '101808.75'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-03' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7625 | '42025.86'           | '110008.72'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-04' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7626 | '51727.27'           | '125514.74'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-05' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7627 | '45501.57'           | '106330.64'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-06' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7628 | '48026.35'           | '117350.16'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-07' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7629 | '51493.51'           | '148743.64000000001' | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-08' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7630 | '40102.230000000003' | '124767.09'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-09' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7631 | '40849.61'           | '114226.42'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-10' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7632 | '50533.83'           | '155022.29'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-11' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+
| L7633 | '42731.83'           | '122845.71'          | Entered Value Changed. | '8353-US FOODS'  | 2        | '2019-12' | SalesCost   |
+-------+----------------------+----------------------+------------------------+------------------+----------+-----------+-------------+

--将电子表格比较结果写入带有NOTEPADD++的SQL更新中--

USE TestingDb
GO

/*
--Create backup of data
insert into OperatingPlan_bak_20181001_1450 select * from OperatingPlan

--Restore backup of data
delete from OperatingPlan
insert into OperatingPlan select * from OperatingPlan_bak_20181001_1450
*/

-- (#1) Create TempTbl for storing the new values
DECLARE 
    @updateOperatingPlan_NetSales as table(
        --  [ProductCode] [nvarchar](12) NOT NULL
        --  ,[Concept] [nvarchar](40) NOT NULL
        [DivisionKey] [int] NOT NULL
        --  ,[PlanYear] [int] NOT NULL
        --  ,[PlanVersion] [int] NOT NULL
        --  ,[Year] [int] NOT NULL
        --  ,[Period] [nvarchar](6) NOT NULL
        ,[PlanDimensionKey] [nvarchar](62) NOT NULL
        ,[PeriodKey] [nvarchar](10) NOT NULL
        --  ,[Volume] [numeric](18, 2) NULL
        ,[NetSales_new] [numeric](18, 2) NULL
        -- ,[SalesCost_new] [numeric](18, 2) NULL
        -- ,[GrossMargin_new] [numeric](18, 2) NULL
    )
DECLARE 
    @updateOperatingPlan_SalesCost as table(
        [DivisionKey] [int] NOT NULL
        ,[PlanDimensionKey] [nvarchar](62) NOT NULL
        ,[PeriodKey] [nvarchar](10) NOT NULL
        ,[SalesCost_new] [numeric](18, 2) NULL
    ) 
DECLARE
    @updateOperatingPlan_GrossMargin as table(
        [GrossMargin_new] [numeric](18, 2) NULL
        ,[DivisionKey] [int] NOT NULL
        ,[PlanDimensionKey] [nvarchar](62) NOT NULL
        ,[PeriodKey] [nvarchar](10) NOT NULL
    )


-- (#2) Insert the new data records into TEMP TABLE
--NetSales_new
INSERT INTO @updateOperatingPlan_NetSales(  
    [NetSales_new] 
    ,[PlanDimensionKey] 
    ,[DivisionKey] 
    ,[PeriodKey] 
    )
    VALUES      --copied from NOTEPAD++ (text) Spreadsheet Compare 2013 (table)
        ('105627.74', '8351-US FOODS', 1, '2019-01')
        ,('133624.49', '8351-US FOODS', 1, '2019-02')
        ,('76481.8', '8351-US FOODS', 1, '2019-03')
        ,('76563.55', '8351-US FOODS', 1, '2019-04')
        ,('92416.79', '8351-US FOODS', 1, '2019-05')
        ,('91324.19', '8351-US FOODS', 1, '2019-06')
        ,('100132.53', '8351-US FOODS', 1, '2019-07')
        ,('136506.1', '8351-US FOODS', 1, '2019-08')
        ,('115062.45', '8351-US FOODS', 1, '2019-09')
        ,('121164.3', '8351-US FOODS', 1, '2019-10')
        ,('135402.20000000001', '8351-US FOODS', 1, '2019-11')
        ,('107210.08', '8351-US FOODS', 1, '2019-12')
        ,('105627.74', '8351-US FOODS', 2, '2019-01')
        ,('133624.49', '8351-US FOODS', 2, '2019-02')
        ,('76481.8', '8351-US FOODS', 2, '2019-03')
        ,('76563.55', '8351-US FOODS', 2, '2019-04')
        ,('92416.79', '8351-US FOODS', 2, '2019-05')
        ,('91324.19', '8351-US FOODS', 2, '2019-06')
        ,('100132.53', '8351-US FOODS', 2, '2019-07')
        ,('136506.1', '8351-US FOODS', 2, '2019-08')
        ,('115062.45', '8351-US FOODS', 2, '2019-09')
        ,('121164.3', '8351-US FOODS', 2, '2019-10')
        ,('135402.20000000001', '8351-US FOODS', 2, '2019-11')
        ,('107210.08', '8351-US FOODS', 2, '2019-12')
        ,('35923.31', '8352-US FOODS', 1, '2019-01')
        ,('53496.21', '8352-US FOODS', 1, '2019-02')
        ,('38034.83', '8352-US FOODS', 1, '2019-03')
        ,('37044.410000000003', '8352-US FOODS', 1, '2019-04')
        ,('46855.07', '8352-US FOODS', 1, '2019-05')
        ,('43004.52', '8352-US FOODS', 1, '2019-06')
        ,('45897.82', '8352-US FOODS', 1, '2019-07')
        ,('52093.120000000003', '8352-US FOODS', 1, '2019-08')
        ,('41391.46', '8352-US FOODS', 1, '2019-09')
        ,('42194.02', '8352-US FOODS', 1, '2019-10')
        ,('48761.78', '8352-US FOODS', 1, '2019-11')
        ,('42905.64', '8352-US FOODS', 1, '2019-12')

--SalesCost_new
INSERT INTO @updateOperatingPlan_SalesCost( 
    [SalesCost_new]
    ,[PlanDimensionKey]
    ,[DivisionKey] 
    ,[PeriodKey] 
    )
    VALUES      --copied from NOTEPAD++ (text) Spreadsheet Compare 2013 (table)
        ('108676.8', '8351-US FOODS', 1, '2019-01')
        ,('142074.69', '8351-US FOODS', 1, '2019-02')
        ,('85073.74', '8351-US FOODS', 1, '2019-03')
        ,('86439.09', '8351-US FOODS', 1, '2019-04')
        ,('101436.04', '8351-US FOODS', 1, '2019-05')
        ,('96085.38', '8351-US FOODS', 1, '2019-06')
        ,('104244.97', '8351-US FOODS', 1, '2019-07')
        ,('134166.29', '8351-US FOODS', 1, '2019-08')
        ,('110776.14', '8351-US FOODS', 1, '2019-09')
        ,('116436.68', '8351-US FOODS', 1, '2019-10')
        ,('139488.53', '8351-US FOODS', 1, '2019-11')
        ,('106055.85', '8351-US FOODS', 1, '2019-12')
        ,('108676.8', '8351-US FOODS', 2, '2019-01')
        ,('142074.69', '8351-US FOODS', 2, '2019-02')
        ,('85073.74', '8351-US FOODS', 2, '2019-03')
        ,('86439.09', '8351-US FOODS', 2, '2019-04')
        ,('101436.04', '8351-US FOODS', 2, '2019-05')
        ,('96085.38', '8351-US FOODS', 2, '2019-06')
        ,('104244.97', '8351-US FOODS', 2, '2019-07')
        ,('134166.29', '8351-US FOODS', 2, '2019-08')
        ,('110776.14', '8351-US FOODS', 2, '2019-09')
        ,('116436.68', '8351-US FOODS', 2, '2019-10')
        ,('139488.53', '8351-US FOODS', 2, '2019-11')
        ,('106055.85', '8351-US FOODS', 2, '2019-12')
        ,('37095.74', '8352-US FOODS', 1, '2019-01')
        ,('57068.24', '8352-US FOODS', 1, '2019-02')
        ,('42449.3', '8352-US FOODS', 1, '2019-03')
        ,('42025.86', '8352-US FOODS', 1, '2019-04')
        ,('51727.27', '8352-US FOODS', 1, '2019-05')
        ,('45501.57', '8352-US FOODS', 1, '2019-06')
        ,('48026.35', '8352-US FOODS', 1, '2019-07')
        ,('51493.51', '8352-US FOODS', 1, '2019-08')
        ,('40102.230000000003', '8352-US FOODS', 1, '2019-09')
        ,('40849.61', '8352-US FOODS', 1, '2019-10')
        ,('50533.83', '8352-US FOODS', 1, '2019-11')
        ,('42731.83', '8352-US FOODS', 1, '2019-12')

--GrossMargin_new
INSERT INTO @updateOperatingPlan_GrossMargin(   
    [GrossMargin_new] 
    ,[PlanDimensionKey] 
    ,[DivisionKey] 
    ,[PeriodKey] 
    )
    VALUES      --copied from NOTEPAD++ (text) Spreadsheet Compare 2013 (table)
        ('-3049.05', '8351-US FOODS', 1, '2019-01')
        ,('-8450.2000000000007', '8351-US FOODS', 1, '2019-02')
        ,('-8591.94', '8351-US FOODS', 1, '2019-03')
        ,('-9875.5400000000009', '8351-US FOODS', 1, '2019-04')
        ,('-9019.25', '8351-US FOODS', 1, '2019-05')
        ,('-4761.1899999999996', '8351-US FOODS', 1, '2019-06')
        ,('-4112.4399999999996', '8351-US FOODS', 1, '2019-07')
        ,('2339.8000000000002', '8351-US FOODS', 1, '2019-08')
        ,('4286.3100000000004', '8351-US FOODS', 1, '2019-09')
        ,('4727.63', '8351-US FOODS', 1, '2019-10')
        ,('-4086.33', '8351-US FOODS', 1, '2019-11')
        ,('1154.23', '8351-US FOODS', 1, '2019-12')
        ,('-3049.05', '8351-US FOODS', 2, '2019-01')
        ,('-8450.2000000000007', '8351-US FOODS', 2, '2019-02')
        ,('-8591.94', '8351-US FOODS', 2, '2019-03')
        ,('-9875.5400000000009', '8351-US FOODS', 2, '2019-04')
        ,('-9019.25', '8351-US FOODS', 2, '2019-05')
        ,('-4761.1899999999996', '8351-US FOODS', 2, '2019-06')
        ,('-4112.4399999999996', '8351-US FOODS', 2, '2019-07')
        ,('2339.8000000000002', '8351-US FOODS', 2, '2019-08')
        ,('4286.3100000000004', '8351-US FOODS', 2, '2019-09')
        ,('4727.63', '8351-US FOODS', 2, '2019-10')
        ,('-4086.33', '8351-US FOODS', 2, '2019-11')
        ,('1154.23', '8351-US FOODS', 2, '2019-12')
        ,('-1172.43', '8352-US FOODS', 1, '2019-01')
        ,('-3572.03', '8352-US FOODS', 1, '2019-02')
        ,('-4414.4799999999996', '8352-US FOODS', 1, '2019-03')
        ,('-4981.45', '8352-US FOODS', 1, '2019-04')
        ,('-4872.2', '8352-US FOODS', 1, '2019-05')
        ,('-2497.0500000000002', '8352-US FOODS', 1, '2019-06')
        ,('-2128.5300000000002', '8352-US FOODS', 1, '2019-07')
        ,('599.6', '8352-US FOODS', 1, '2019-08')
        ,('1289.23', '8352-US FOODS', 1, '2019-09')
        ,('1344.41', '8352-US FOODS', 1, '2019-10')
        ,('-1772.05', '8352-US FOODS', 1, '2019-11')
        ,('173.8', '8352-US FOODS', 1, '2019-12')

;WITH cteOperatingPlanUpdates as 
(
    SELECT 
        t1.PlanDimensionKey
        ,t1.DivisionKey
        ,t1.PeriodKey
        ,t1.NetSales_new
        ,t2.SalesCost_new
        ,t3.GrossMargin_new
    FROM 
        @updateOperatingPlan_NetSales t1
        LEFT OUTER JOIN @updateOperatingPlan_SalesCost t2 
            ON t1.PlanDimensionKey = t2.PlanDimensionKey 
                AND t1.DivisionKey = t2.DivisionKey 
                AND t1.PeriodKey = t2.PeriodKey
        LEFT OUTER JOIN @updateOperatingPlan_GrossMargin t3
            ON t1.PlanDimensionKey = t3.PlanDimensionKey 
                AND t1.DivisionKey = t3.DivisionKey 
                AND t1.PeriodKey = t3.PeriodKey
)

--select * from cteOperatingPlanUpdates

--/*
-- (#3) Update TargetTbl values with TempTbl values 
UPDATE [dbo].[OperatingPlan]
    SET 
        [NetSales] = source.NetSales_new
        ,[SalesCost] = source.SalesCost_new
        ,[GrossMargin] = source.GrossMargin_new

    FROM cteOperatingPlanUpdates source
    WHERE 
        OperatingPlan.[PlanDimensionKey] = source.PlanDimensionKey
        AND OperatingPlan.[DivisionKey] = source.DivisionKey
        AND OperatingPlan.[PeriodKey] = source.PeriodKey
GO
--*/

- SherlockSpreadsheets