0

Hei,

I am new to SQL so my apologies if this question is too basic.

I have a solution with the following tables and logic for SCD Type 1.

-- Source table

CREATE TABLE table_source (
recipe_id INT NOT NULL,
recipe_name VARCHAR(30) NOT NULL,
HashValue VARCHAR(30) NOT NULL)


INSERT INTO table_source
(recipe_id, recipe_name, HashValue) 
VALUES 
(1,'Tacos','1039479aa20'),
(2,'TomatoSoup','1039479aa21'),
(3,'GrilledCheese', '1039479aa22')

--Target table

CREATE TABLE table_target (
recipe_id INT NOT NULL,
recipe_name VARCHAR(30) NOT NULL,
HashValue VARCHAR(30) NOT NULL)

INSERT INTO table_target
(recipe_id, recipe_name, HashValue) 
VALUES 
(1,'Tacos','1039479aa20'),
(2,'TomatoSoup','1039479aa21'),
(3,'GrilledCheese', '1039479aa22'),
(4,'BakedCheese', '1039479aa23')

--TYPE 1 SLOWLY CHANGING DIMENSION

MERGE INTO table_target AS TARGET
USING (

SELECT  recipe_id,
        recipe_name,
        HashValue
 FROM fram_source
 ) AS SOURCE
 ON SOURCE.recipe_id  = TARGET.recipe_id
 WHEN MATCHED
 AND (ISNULL(TARGET.HashValue, '') <> ISNULL(SOURCE.HashValue, ''))
 THEN
 UPDATE
 SET
   TARGET.recipe_id  = SOURCE.recipe_id 
  ,TARGET.recipe_name = SOURCE.recipe_name,
   TARGET.[HashValue] = SOURCE.[HashValue]
WHEN NOT MATCHED
THEN
INSERT (recipe_id,
       recipe_name,
       HashValue
  )
VALUES (

   SOURCE.recipe_id
  ,SOURCE.  recipe_name
   , SOURCE.[HashValue]
 )  
  OUTPUT
  $action,
  inserted.*,
  deleted.*

;

I want my target table to be identical to my source table, but this script does not erase extra values in target table. My question is, how do I make this scrip to delete values that are in target table that are not in source table?

db_noob
  • 119
  • 2
  • 11
  • 1
    Huh ? Dont you want to just delete anything that NOT EXISTS ? `DELETE FROM target_table WHERE NOT EXISTS (SELECT ... FROM target_table WHERE x = source_table.x...);` – JonH Aug 30 '21 at 15:59
  • Thank you, yes. I tested that and it works but I needed to add this functionality to my script so I found the solution. Thanks for your help! – db_noob Aug 31 '21 at 02:16

1 Answers1

0

I found the solution here, I was missing the condition for delete: https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

db_noob
  • 119
  • 2
  • 11