CODEWITHSUNDEEP
sqlscd
db_noob
db_noob

Reputation: 135

Slowly Changing dimension type 1 in SQL server - deleting data on target table

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?

Upvotes: 0

Views: 2476

Answers (1)

db_noob
db_noob

Reputation: 135

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/

Upvotes: 0

Related Questions