begin tran

declare @attributeID uniqueidentifier = 'f5c9fe3d-7380-e411-b81e-a45d36c58568'

insert into common.attributeValue(attributeValueID, attributeGroupComponentRelationID, attributeID, textValue, avRemoteSource, avRemoteID, status, addDate, addUsername)
--values(@id, 'A84AB727-8C80-E411-B81E-A45D36C58568', @attributeID, '731', 'product', 'C19A4B61-E70E-4586-ADA6-001FAE022108', 0, getutcdate(), 'demo')
select newID(), 'A84AB727-8C80-E411-B81E-A45D36C58568', @attributeID, p.productCode, 'product', p.productID, 0, GETUTCDATE(), 'demo'
from product.product p
left join common.attributeValue av on av.attributeID = @attributeID and av.avRemoteID = p.productID
where p.productCode is not null and isnull(p.productCode, newId()) != isnull(av.textValue, newId())
and av.attributeValueID is null

update av
set av.textValue = p.productCode
from product.product p
left join common.attributeValue av on av.attributeID = @attributeID and av.avRemoteID = p.productID
where p.productCode is not null and isnull(p.productCode, newId()) != isnull(av.textValue, newId())
and av.attributeValueID is not null and av.textValue is null

select p.productCode, av.textValue, * from product.product p
left join common.attributeValue av on av.attributeID = @attributeID and av.avRemoteID = p.productID
where p.productCode is not null and isnull(p.productCode, newId()) != isnull(av.textValue, newId())
and av.attributeValueID is not null and av.textValue is not null

select count(*) from product.product p
left join common.attributeValue av on av.attributeID = @attributeID and av.avRemoteID = p.productID
where p.productCode is not null and isnull(p.productCode, newId()) != isnull(av.textValue, newId())

rollback tran
