Datanory Trial

select * from #temptable --320
select * from product_item where org_id = 'FERNTEA' order by updated_date desc --319

select * from #TempTable where item_code not in
(select item_code from product_item where org_id = 'FERNTEA') --2

select * from product_group where org_id = 'FERNTEA' order by updated_date desc
select * from #TempTable where group_code not in
(select group_code from product_group where org_id = 'FERNTEA')
--INSERT into product_group (group_id, group_code, group_name, group_short_name, group_desc, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
SELECT NEWID, x.group_code, x.group_code, x.group_code, x.group_code, 'Active', GETDATE, 'Support', GETDATE, 'Support', 'FERNTEA', 'FERNTEA'
FROM
(
SELECT di.group_code from #TempTable di
--WHERE di.process_id = @ProcessId
GROUP BY di.group_code
) x
WHERE NOT EXISTS
--and x.process_id = @ProcessId
--and x.org_id = @OrganizationID

select * from product_sub_group where org_id = 'FERNTEA' order by updated_date desc
select * from #TempTable where sub_group_code not in
(select sub_group_code from product_sub_group where org_id = 'FERNTEA')
--INSERT into product_sub_group (sub_group_id, sub_group_code, sub_group_name, sub_group_short_name, sub_group_desc, group_id, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
SELECT NEWID, x.sub_group_code, x.sub_group_code, x.sub_group_code, x.sub_group_code, g.group_id, 'Active', GETDATE, 'Support', GETDATE, 'Support', 'FERNTEA', 'FERNTEA'
FROM
(
SELECT di.sub_group_code, di.group_code FROM #TempTable di
--WHERE di.process_id = @ProcessId
GROUP BY di.sub_group_code, di.group_code
) x
inner join product_group g on g.group_code = x.group_code and g.org_id = 'FERNTEA'
WHERE NOT EXISTS
--and x.process_id = @ProcessId
--and x.org_id = @OrganizationID

select * from product_item where org_id = 'FERNTEA' order by updated_date desc
select * from #TempTable where item_code not in
(select item_code from product_item where org_id = 'FERNTEA') --321

select a.group_id, c.group_id, a.sub_group_id, d.sub_group_id, * from product_item a
inner join #TempTable b on b.item_code = a.item_code and a.org_id = 'FERNTEA'
inner join product_group c on c.group_code = b.group_code and c.org_id = 'FERNTEA'
inner join product_sub_group d on d.sub_group_code = b.sub_group_code and d.group_id = c.group_id and d.org_id = 'FERNTEA'
where a.org_id = 'FERNTEA'

--update a set a.group_id = c.group_id, a.sub_group_id = d.sub_group_id, a.updated_date = getdate(), updated_by = 'Support'
--from product_item a
--inner join #TempTable b on b.item_code = a.item_code and a.org_id = 'FERNTEA'
--inner join product_group c on c.group_code = b.group_code and c.org_id = 'FERNTEA'
--inner join product_sub_group d on d.sub_group_code = b.sub_group_code and d.group_id = c.group_id and d.org_id = 'FERNTEA'
--where a.org_id = 'FERNTEA'

select * from product_group where org_id = 'FERNTEA' and group_id not in
(select group_id from product_item where org_id = 'FERNTEA')

--select * into _product_group_20240701 from product_group

--delete from product_group where org_id = 'FERNTEA' and group_id not in
--(select group_id from product_item where org_id = 'FERNTEA')

select * from product_sub_group where org_id = 'FERNTEA' and sub_group_id not in
(select sub_group_id from product_item where org_id = 'FERNTEA')

--select * into _product_sub_group_20240701 from product_sub_group

--delete from product_sub_group where org_id = 'FERNTEA' and sub_group_id not in
--(select sub_group_id from product_item where org_id = 'FERNTEA')