Hi All,
I have a big csv file which I'm putting into a temp table with the bulk insert command. This works fine.
What I'm trying to do then is loop through the temp table to either update a matching product in the product table or, if the product doesn't exist, add it to the product table and update my audit log to say whether the product was added or, if it was updated, what fields were updated and the values they were updated from and to.
Is a cursor the best, most efficient way to do this? This is the cursor I have at the moment but because you cannot use CASE statements in a cursor I'm unsure on getting the before and after fields when doing an update.
declare @sku varchar(50)
declare @Description varchar(255)
declare @QtyOnHand decimal(18,2)
declare @price money
declare @LastCost money
declare @value money
declare @Unit varchar(25)
declare @branchguid varchar(32)
Declare updateproduct cursor for
select
sku,
description,
Qtyonhand,
lastcost,
value,
branchguid from tempProductImport
Open updateproduct
fetch next from updateproduct into
@sku,@Description,@QtyOnHand,@LastCost,@value,@branchguid
while @@fetch_status = 0
IF not exists (select * from product where sku = @sku and branchguid = @branchguid)
begin
insert into product(productguid,SKU,ProductDescription,QuantityOnHand,active,lastcost,value,vatguid,versionnumber,branchguid,LastUpdated)
values
(replace(newid(),'-',''),@sku,@Description,@QtyOnHand,1,@LastCost,@value,(select vatguid from vat where vatdescription = 'Standard'),1,@branchguid,getdate())
--update my audit log.
insert into audit(AuditGUID,auditdateandtime,audittype,audituser,auditnote)
values
(replace(newid(),'-',''),getdate(),'Product Import','NPA','Product added from import ' + @SKU + ' Description ' + @Description)
fetch next from updateproduct into
@sku,@Description,@QtyOnHand,@LastCost,@value,@branchguid
end
else
begin
update product set
--sku = @sku,
--productdescription = @description,
QuantityOnHand = cast(@QtyOnHand as decimal),
lastcost = @lastcost,
value = @value
--BranchGUID = @branchguid
where sku = @sku and branchguid = @branchguid
--update my audit log.
insert into audit(AuditGUID,auditdateandtime,audittype,audituser,auditnote)
values
(replace(newid(),'-',''),getdate(),'Product Import','NPA','Product updated from import ' + @SKU + ' Description ' + @Description)
fetch next from updateproduct into
@sku,@Description,@QtyOnHand,@LastCost,@value,@branchguid
end
close updateproduct
deallocate updateproduct
Thanks in advance.
Since I dont have actual tables and data, the code is untested.
That said, I believe that the following code will do the UPSERT and AUDIT without the need for a cursor
DECLARE @vatguid uniqueidentifier = (SELECT vatguid FROM vat WHERE vatdescription = 'Standard');
UPDATE p
SET p.QuantityOnHand = CAST(t.Qtyonhand AS decimal)
, p.lastcost = t.lastcost
, p.value = t.value
OUTPUT REPLACE( NEWID(), '-', '' ), GETDATE(), 'Product Import', 'NPA', 'Product updated from import ' + INSERTED.sku + ' Description ' + INSERTED.Description
INTO audit (AuditGUID, auditdateandtime, audittype, audituser, auditnote)
FROM product AS p
INNER JOIN tempProductImport AS t
ON p.sku = t.sku
AND p.branchguid = t.branchguid;
INSERT INTO product ( productguid, SKU, ProductDescription, QuantityOnHand, active, lastcost, VALUE, vatguid, versionnumber, branchguid, LastUpdated )
OUTPUT INSERTED.productguid, GETDATE(), 'Product Import', 'NPA', 'Product added from import ' + INSERTED.sku + ' Description ' + INSERTED.Description
INTO audit (AuditGUID, auditdateandtime, audittype, audituser, auditnote)
SELECT REPLACE( NEWID(), '-', '' ), t.sku, t.Description, t.QtyOnHand, 1, t.LastCost, t.value, @vatguid, 1, t.branchguid, GETDATE()
FROM tempProductImport AS t
WHERE NOT EXISTS (select 1 FROM product AS p WITH (XLOCK, HOLDLOCK)
WHERE p.sku = t.sku
AND p.branchguid = t.branchguid
);
January 27, 2022 at 4:49 pm
This was removed by the editor as SPAM
January 28, 2022 at 6:18 am
Sorry about deleting your question. I clicked the wrong button.
To get the old value, use DELETED.YourFieldname
To get the new value, use INSERTED.YourFieldName
So, I would alter the description of the UPDATE portion as follows
UPDATE p
SET p.QuantityOnHand = CAST(t.Qtyonhand AS DECIMAL)
, p.lastcost = t.lastcost
, p.value = t.value
OUTPUT REPLACE( NEWID(), '-', '' ), GETDATE(), 'Product Import', 'NPA'
, 'Product updated from import ' + INSERTED.sku + ' Description ' + INSERTED.Description
+ ' OldCost = ' + CONVERT(varchar(20), DELETED.lastcost)
+ ' NewCost = ' + CONVERT(varchar(20), INSERTED.lastcost)
INTO audit (AuditGUID, auditdateandtime, audittype, audituser, auditnote)
FROM product AS p
INNER JOIN tempProductImport AS t
ON p.sku = t.sku
AND p.branchguid = t.branchguid;
January 28, 2022 at 9:13 am
Brilliant, thank you very much for your time Des.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply