1

I am kinda confused with the way merge statement has to be written in my scenario to load data from a table into dimension and fact tables.

Below is the merge statement where I am trying to load data into my DIM product table from the JSON table but I am getting NULL values loaded.

Am I doing something wrong with the merge and should I not merge on primary key and instead on the fields like cityname

I am having the same issue while trying to load data into the fact table

Could someone please help?

merge into dim_product as a using (select productID, product from jsontable) as b
on b.productID = a.productID
when matched then update a.productID = b.productID
when not matched then insert (productID, product) values (b.productID, b.product));

Below is the Existing Dimension Products table

enter image description here

Below is the sample JSON from which I am trying to merge and insert the new record Mango into my DIM table and also populate the PRODUCTID into my fact table

enter image description here

Below is the fact table

enter image description here

SMR
  • 401
  • 4
  • 15
  • 1
    This depends on the initial table structure (`CREATE TABLE` statements), the precise data (`INSERT` statements), detail about the exact result of the `MERGE` operation and exactly which rows/values were unexpected, plus the exact expected result. A minimal reproducible test case is often critical in solving a problem without guessing. Try to update the question with _all_ the above detail. – Jon Armstrong Oct 26 '21 at 21:34
  • Hi @JonArmstrong, Thanks for that, I have updated my question with sample images. – SMR Oct 26 '21 at 22:40
  • Why are you updating the primary key, when you find a matching row, you should update the product, not the productID. I think the key is how do you extract data from json, can you show what this data looks like and how do you extract it? – Michael Golos Oct 27 '21 at 02:57
  • Your json table doesn’t have a product id column. Please review your SQL and sample tables in your question and make sure they are consistent – NickW Oct 27 '21 at 08:03
  • The query you provided does not match the sample data shared. As Nick mentioned, there is no productId in the JSON data. Also, there is no "product" column, so "select productID, product from jsontable" is not correct too, it should include the Description field. Please fix your question. – Eric Lin Oct 27 '21 at 11:45

1 Answers1

0

If we do not have ProductID on the source and set it only in Dim_Product, we should use the business key. In your case, ProductName is the business key. The solution is simple, when you are doing MERGE you should use ProductName as the key instead of ProductID.

Your MERGE should look similar to this:

merge into dim_product as a using (select ProductName from jsontable) as b
on b.ProductName = a.ProductName
when not matched then insert (ProductName) values (b.ProductName));

If you have more attributes describing the Product, they should be modified inside the MERGE.

Michael Golos
  • 1,814
  • 1
  • 6
  • 16