Login/Register
Visual Studio SSDT Data Compare how to compare two tables in a single database
Asked Answered
Solved sql-servervisual-studio-2012sql-server-data-toolsdata-comparison
F

3

8

Trying to do something simple Data Compare in SSDT but proving a bit hard.

In one database, I have two tables I want to compare.

These tables have the same schema, just different table names. And I just want to see if this tool will give me a nice way to compare the data in both.

I.e.

tblOutput
tblOutput_210314

But this picking of two tables to compare against each other in a single database I can't see how to achieve.

Seems like you can only pick a table name which exists in both source and target databases. Since my source and target database is the same, I am basically comparing my table to itself ?

Anyone know of a way to achieve this with Data Compare ?

  • Visual Studio 2012
  • SQL Server Data Tools
  • Data Compare
Fiona answered 6/3, 2014 at 9:46 Comment(0)
R
8

Data Compare only supports comparing 2 different databases with matching schemas. Unfortunately you can't do what you are looking to with Data Compare. From the help documentation:

Requirements

When you compare data in a table or view, the table or view in the source database must share several attributes with a table or view in the target database. Tables and views that do not meet the following criteria are not compared and do not appear on the second page of the New Data Comparison wizard:

  • Tables must have matching column names that have compatible data types.
  • Names of tables, views, and owners are case-sensitive.
  • Tables must have the same primary key, unique index, or unique constraint.
  • Views must have the same unique, clustered index.
  • You can compare a table with a view only if they have the same name.
Russi answered 6/3, 2014 at 17:9 Comment(1)
While I believe this is a good answer, because it is showing the limitations of Visual Studio's data compare tool, it does not answer how else it can be achieved. I have the same issue - the need to compare two tables in the same database created by different versions of a view I wrote (I created those tables by SELECT * INTO tmpTable1 FROM myViewV1 and SELECT * INTO tmpTable2 FROM myViewV2 which does not work due to the limitations of the data compare tool). So - is there a practical way to do this? (Note this only works if you create a PK later as SELECT * INTO does not do that.) – Gillis
A
2

A work around for the tool limitation. If you have two tables with columns in common that need to be compared. One solution requires duplicating the database and comparing views.

For this example we have a database called CoffeeTableBooksDatabase with two tables, table_myBooks and table_aFriendsBooks with common columns authors and titles that need to be compared.

Duplicate the database with backup then do a restore:

  1. Backup the Database (ex: CoffeeTableBooksDatabase)
  2. Restore the Database with a different name (ex: AnotherCoffeeTableBooksDatabase)

Create a common view:

  1. Create a view (ex: view_SomeBooks) with the same name in both databases
  2. In the original database (ex: CoffeeTableBooksDatabase), add the first table (ex: table_myBooks) to the view (ex: view_SomeBooks) with all the columns common to both tables (ex: authors and titles)
  3. In the duplicate database (ex: AnotherCoffeeTableBooksDatabase), add the second table (ex: table_aFriendsBooks) to the view (ex: view_SomeBooks) with all the columns common to both tables (ex: authors and titles)

Now you can compare both databases by just looking at the views comparison. This is not a recommended solution but it works using the tool and its current limitations.

Ancient answered 6/7, 2017 at 17:45 Comment(0)
D
0

Another person here with the same issue in SSDT Data Compare. One thing you could do is use the Spreadsheet Compare 2013 program to compare the 2 tables in Excel workbook sheets.

1) Dump the data from SQL Server table to 2 separate excel workbooks with the same sheet name to 'Sheet 1'.

2) Run the Spreadsheet Compare program, enter the filepath to the 2 workbooks created in Step1.

3) Use the export results option. Differences will be exported to Excel.

NOTE: Each value change for each field will be a new record in the "Compare Export"

4) To the exported results (Excel) you would add a field with the key fields from the table. I did this using INDIRECT formula and replacing the Range Value column letter (EXPL: 'J7562' from 'J' to 'B'). The complete formula looks as follows. I had to do this a few times for other key fields needed. 

--Key Value from the dataset, [PlanDimensionKey]
(E2) ="'" & INDIRECT("'[JET upload LAST change.xlsx]dbo.OperatingPlan (import)'" & "!H" & RIGHT(B2,4)) & "'" 

--Key Value from the dataset, [Division]
(F2) =INDIRECT("'[JET upload LAST change.xlsx]dbo.OperatingPlan (import)'" & "!C" & RIGHT(B2,4))

--Key Value from the dataset, [PeriodKey]
(G2) ="'" & INDIRECT("'[JET upload LAST change.xlsx]dbo.OperatingPlan (import)'" & "!I" & RIGHT(B2,4)) & "'"

--Returns the FIELD LABEL for each value difference; typed in columnar ordinal position
(H2) =CHOOSE(COLUMN(INDIRECT(LEFT(B11,1)&"1")),
"ProductCode", "Concept", "DivisionKey", "PlanYear", "PlanVersion", "Year", "Period", "PlanDimensionKey", "PeriodKey", "NetSales", "Volume", "SalesCost", "GrossMargin")

5) You could then use a text editor (NotePad++) to paste in the table data and write the SQL UPDATE statement

  • Replace tabs with a comma in NOTEPAD++... replace \t with ,
  • Add values parenthesis for each record in NOTEPADD++... replace \r\n with )\r\n,(

Not the easiest thing I've ever done, but it is a serviceable way to compare data and write SQL changes. I was able to modify 504 values fairly simply with this method. Changes:= 7 SKU * 3 Columns * 12 Months * 2 divisions = 504 changes. It's too bad that VS SSDT cannot compare tables with schema differences.

--Spreadsheet Compare 2013 export results with added key fields--

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

--Spreadsheet Compare results written into SQL Update with NOTEPADD++--

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
--*/
Dorthadorthea answered 1/10, 2018 at 22:1 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.