January 18, 2021 at 11:34 pm
Hi,
I'm having some problems with several Data Factory Copy Data jobs using stored procedures performing an upsert. I am using the method described on this page by Taygan
https://www.taygan.co/blog/2018/04/20/upsert-to-azure-sql-db-with-azure-data-factory
I am using this technique to copy data from tables in Azure SQL Server to our On-Prem SQL Server.
When I add the delete clause to the merge statement I get some very strange results on the target table (on-prem SQL Server). For some reason the stored procedure will delete way more records on the target table than it should. When I remove the delete clause it works fine and inserts new records and updates existing records, but I need to be able to delete records to in the target so it is the same as the source table.
I thought it could be a bug in SQL Server, so I updated it to the latest version of 2016. I also ran the same test re-creating it on the on-prem SQL Server and the merge statement with delete clause and it works fine as expected.
You may ask why do I need to remove records to match the source table? The reason why is that the tables I am copying to on-prem has sales order line, purchase order line, etc data. For example, if a sales order line gets deleted (which one or two lines get deleted a day), I need the source to be updated to match.
Should I not be using Copy Data in Data Factories when I need to be able to remove records in the target table to match the source? Any help would be appreciated. I've been working on this for quite sometime and I'm stumped.
Thanks,
Tim
Here is my stored procedure if helpful
USE [D365FO]
GO
/****** Object: StoredProcedure [dbo].[spUpsertSalesOrderLineV2Staging] Script Date: 1/18/2021 3:29:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spUpsertSalesOrderLineV2Staging] @salesOrderLineV2Staging SalesOrderLineV2StagingType READONLY
AS
BEGIN
MERGE SalesOrderLineV2Staging AS target_sqlonprem
USING @salesOrderLineV2Staging AS source_sqlazure
ON (target_sqlonprem.INVENTORYLOTID = source_sqlazure.INVENTORYLOTID
AND target_sqlonprem.DATAAREAID = source_sqlazure.DATAAREAID
AND target_sqlonprem.PARTITION = source_sqlazure.PARTITION)
WHEN MATCHED THEN
UPDATE SET
DEFAULTLEDGERDIMENSIONDISPLAYVALUE = source_sqlazure.DEFAULTLEDGERDIMENSIONDISPLAYVALUE,
MAINACCOUNTIDDISPLAYVALUE = source_sqlazure.MAINACCOUNTIDDISPLAYVALUE,
ISDELIVERYADDRESSORDERSPECIFIC = source_sqlazure.ISDELIVERYADDRESSORDERSPECIFIC,
ORDERLINEREFERENCE = source_sqlazure.ORDERLINEREFERENCE,
DEFINITIONGROUP = source_sqlazure.DEFINITIONGROUP,
EXECUTIONID = source_sqlazure.EXECUTIONID,
ISSELECTED = source_sqlazure.ISSELECTED,
TRANSFERSTATUS = source_sqlazure.TRANSFERSTATUS,
SALESORDERNUMBER = source_sqlazure.SALESORDERNUMBER,
INVENTORYLOTID = source_sqlazure.INVENTORYLOTID,
SALESORDERLINESTATUS = source_sqlazure.SALESORDERLINESTATUS,
ISLINESTOPPED = source_sqlazure.ISLINESTOPPED,
CUSTOMERSLINENUMBER = source_sqlazure.CUSTOMERSLINENUMBER,
CUSTOMSDOCUMENTDATE = source_sqlazure.CUSTOMSDOCUMENTDATE,
CUSTOMSDOCUMENTNUMBER = source_sqlazure.CUSTOMSDOCUMENTNUMBER,
CUSTOMSDOCUMENTNAME = source_sqlazure.CUSTOMSDOCUMENTNAME,
SALESORDERPROMISINGMETHOD = source_sqlazure.SALESORDERPROMISINGMETHOD,
DELIVERYADDRESSNAME = source_sqlazure.DELIVERYADDRESSNAME,
DELIVERYMODECODE = source_sqlazure.DELIVERYMODECODE,
DELIVERYTERMSID = source_sqlazure.DELIVERYTERMSID,
EXTERNALITEMNUMBER = source_sqlazure.EXTERNALITEMNUMBER,
BOMID = source_sqlazure.BOMID,
ITEMNUMBER = source_sqlazure.ITEMNUMBER,
ROUTEID = source_sqlazure.ROUTEID,
LINEAMOUNT = source_sqlazure.LINEAMOUNT,
LINEDISCOUNTAMOUNT = source_sqlazure.LINEDISCOUNTAMOUNT,
LINEDISCOUNTPERCENTAGE = source_sqlazure.LINEDISCOUNTPERCENTAGE,
MULTILINEDISCOUNTAMOUNT = source_sqlazure.MULTILINEDISCOUNTAMOUNT,
MULTILINEDISCOUNTPERCENTAGE = source_sqlazure.MULTILINEDISCOUNTPERCENTAGE,
LINEDESCRIPTION = source_sqlazure.LINEDESCRIPTION,
ALLOWEDOVERDELIVERYPERCENTAGE = source_sqlazure.ALLOWEDOVERDELIVERYPERCENTAGE,
PACKINGUNITSYMBOL = source_sqlazure.PACKINGUNITSYMBOL,
WILLAUTOMATICINVENTORYRESERVATIONCONSIDERBATCHATTRIBUTES = source_sqlazure.WILLAUTOMATICINVENTORYRESERVATIONCONSIDERBATCHATTRIBUTES,
WILLREBATECALCULATIONEXCLUDELINE = source_sqlazure.WILLREBATECALCULATIONEXCLUDELINE,
SALESREBATEPRODUCTGROUPID = source_sqlazure.SALESREBATEPRODUCTGROUPID,
INTRASTATPORTID = source_sqlazure.INTRASTATPORTID,
SALESPRICEQUANTITY = source_sqlazure.SALESPRICEQUANTITY,
EINVOICEPROPERTYNUMBER = source_sqlazure.EINVOICEPROPERTYNUMBER,
CUSTOMERREQUISITIONNUMBER = source_sqlazure.CUSTOMERREQUISITIONNUMBER,
CONFIRMEDRECEIPTDATE = source_sqlazure.CONFIRMEDRECEIPTDATE,
REQUESTEDRECEIPTDATE = source_sqlazure.REQUESTEDRECEIPTDATE,
INVENTORYRESERVATIONMETHOD = source_sqlazure.INVENTORYRESERVATIONMETHOD,
COMMISSIONSALESREPRESENTATIVEGROUPID = source_sqlazure.COMMISSIONSALESREPRESENTATIVEGROUPID,
FIXEDPRICECHARGES = source_sqlazure.FIXEDPRICECHARGES,
SALESPRICE = source_sqlazure.SALESPRICE,
ORDEREDSALESQUANTITY = source_sqlazure.ORDEREDSALESQUANTITY,
SALESUNITSYMBOL = source_sqlazure.SALESUNITSYMBOL,
SERVICEORDERNUMBER = source_sqlazure.SERVICEORDERNUMBER,
CONFIRMEDSHIPPINGDATE = source_sqlazure.CONFIRMEDSHIPPINGDATE,
REQUESTEDSHIPPINGDATE = source_sqlazure.REQUESTEDSHIPPINGDATE,
INTRASTATSTATISTICSPROCEDURECODE = source_sqlazure.INTRASTATSTATISTICSPROCEDURECODE,
ISINTRASTATTRIANGULARDEAL = source_sqlazure.ISINTRASTATTRIANGULARDEAL,
SALESTAXGROUPCODE = source_sqlazure.SALESTAXGROUPCODE,
SALESTAXITEMGROUPCODE = source_sqlazure.SALESTAXITEMGROUPCODE,
INTRASTATTRANSACTIONCODE = source_sqlazure.INTRASTATTRANSACTIONCODE,
INTRASTATTRANSPORTMODECODE = source_sqlazure.INTRASTATTRANSPORTMODECODE,
ALLOWEDUNDERDELIVERYPERCENTAGE = source_sqlazure.ALLOWEDUNDERDELIVERYPERCENTAGE,
SALESPRODUCTCATEGORYNAME = source_sqlazure.SALESPRODUCTCATEGORYNAME,
NGPCODE = source_sqlazure.NGPCODE,
PRODUCTCONFIGURATIONID = source_sqlazure.PRODUCTCONFIGURATIONID,
ITEMBATCHNUMBER = source_sqlazure.ITEMBATCHNUMBER,
PRODUCTCOLORID = source_sqlazure.PRODUCTCOLORID,
SHIPPINGWAREHOUSEID = source_sqlazure.SHIPPINGWAREHOUSEID,
SHIPPINGSITEID = source_sqlazure.SHIPPINGSITEID,
PRODUCTSIZEID = source_sqlazure.PRODUCTSIZEID,
ORDEREDINVENTORYSTATUSID = source_sqlazure.ORDEREDINVENTORYSTATUSID,
PRODUCTSTYLEID = source_sqlazure.PRODUCTSTYLEID,
FORMATTEDDELVERYADDRESS = source_sqlazure.FORMATTEDDELVERYADDRESS,
DELIVERYBUILDINGCOMPLIMENT = source_sqlazure.DELIVERYBUILDINGCOMPLIMENT,
DELIVERYADDRESSCITY = source_sqlazure.DELIVERYADDRESSCITY,
DELIVERYADDRESSCOUNTRYREGIONID = source_sqlazure.DELIVERYADDRESSCOUNTRYREGIONID,
DELIVERYADDRESSCOUNTRYREGIONISOCODE = source_sqlazure.DELIVERYADDRESSCOUNTRYREGIONISOCODE,
DELIVERYADDRESSCOUNTYID = source_sqlazure.DELIVERYADDRESSCOUNTYID,
DELIVERYADDRESSDESCRIPTION = source_sqlazure.DELIVERYADDRESSDESCRIPTION,
DELIVERYADDRESSDISTRICTNAME = source_sqlazure.DELIVERYADDRESSDISTRICTNAME,
DELIVERYADDRESSDUNSNUMBER = source_sqlazure.DELIVERYADDRESSDUNSNUMBER,
ISDELIVERYADDRESSPRIVATE = source_sqlazure.ISDELIVERYADDRESSPRIVATE,
DELIVERYADDRESSLATITUDE = source_sqlazure.DELIVERYADDRESSLATITUDE,
DELIVERYADDRESSLOCATIONID = source_sqlazure.DELIVERYADDRESSLOCATIONID,
DELIVERYADDRESSLONGITUDE = source_sqlazure.DELIVERYADDRESSLONGITUDE,
DELIVERYADDRESSPOSTBOX = source_sqlazure.DELIVERYADDRESSPOSTBOX,
DELIVERYADDRESSSTATEID = source_sqlazure.DELIVERYADDRESSSTATEID,
DELIVERYADDRESSSTREET = source_sqlazure.DELIVERYADDRESSSTREET,
DELIVERYADDRESSSTREETNUMBER = source_sqlazure.DELIVERYADDRESSSTREETNUMBER,
DELIVERYADDRESSZIPCODE = source_sqlazure.DELIVERYADDRESSZIPCODE,
DELIVERYADDRESSCITYINKANA = source_sqlazure.DELIVERYADDRESSCITYINKANA,
DELIVERYADDRESSSTREETINKANA = source_sqlazure.DELIVERYADDRESSSTREETINKANA,
DELIVERYADDRESSTIMEZONE = source_sqlazure.DELIVERYADDRESSTIMEZONE,
INTRASTATCOMMODITYCODE = source_sqlazure.INTRASTATCOMMODITYCODE,
EINVOICEDIMENSIONACCOUNTCODE = source_sqlazure.EINVOICEDIMENSIONACCOUNTCODE,
WITHHOLDINGTAXGROUPCODE = source_sqlazure.WITHHOLDINGTAXGROUPCODE,
ITEMWITHHOLDINGTAXCODEGROUPCODE = source_sqlazure.ITEMWITHHOLDINGTAXCODEGROUPCODE,
CREDITNOTEREASONCODE = source_sqlazure.CREDITNOTEREASONCODE,
INTRASTATSTATISTICVALUE = source_sqlazure.INTRASTATSTATISTICVALUE,
INTRASTATSPECIALMOVEMENTCODE = source_sqlazure.INTRASTATSPECIALMOVEMENTCODE,
DELIVERYSALESTAXGROUPCODE = source_sqlazure.DELIVERYSALESTAXGROUPCODE,
DELIVERYSALESTAXITEMGROUPCODE = source_sqlazure.DELIVERYSALESTAXITEMGROUPCODE,
DELIVERYCFOPCODE = source_sqlazure.DELIVERYCFOPCODE,
CFOPCODE = source_sqlazure.CFOPCODE,
SUFRAMADISCOUNTPERCENTAGE = source_sqlazure.SUFRAMADISCOUNTPERCENTAGE,
SERVICEFISCALINFORMATIONCODE = source_sqlazure.SERVICEFISCALINFORMATIONCODE,
FISCALDOCUMENTTYPEID = source_sqlazure.FISCALDOCUMENTTYPEID,
LINECREATIONSEQUENCENUMBER = source_sqlazure.LINECREATIONSEQUENCENUMBER,
GIFTCARDBUYEREMAIL = source_sqlazure.GIFTCARDBUYEREMAIL,
GIFTCARDBUYERNAME = source_sqlazure.GIFTCARDBUYERNAME,
GIFTCARDGIFTMESSAGE = source_sqlazure.GIFTCARDGIFTMESSAGE,
GIFTCARDNUMBER = source_sqlazure.GIFTCARDNUMBER,
GIFTCARDRECIPIENTEMAIL = source_sqlazure.GIFTCARDRECIPIENTEMAIL,
GIFTCARDRECIPIENTNAME = source_sqlazure.GIFTCARDRECIPIENTNAME,
GIFTCARDTYPE = source_sqlazure.GIFTCARDTYPE,
SKIPCREATEAUTOCHARGES = source_sqlazure.SKIPCREATEAUTOCHARGES,
PARTITION = source_sqlazure.PARTITION,
ORDEREDCATCHWEIGHTQUANTITY = source_sqlazure.ORDEREDCATCHWEIGHTQUANTITY,
PROJECTCATEGORYID = source_sqlazure.PROJECTCATEGORYID,
PROJECTID = source_sqlazure.PROJECTID,
PROJECTLINEPROPERTYID = source_sqlazure.PROJECTLINEPROPERTYID,
CURRENCYCODE = source_sqlazure.CURRENCYCODE,
SHIPPINGWAREHOUSELOCATIONID = source_sqlazure.SHIPPINGWAREHOUSELOCATIONID,
REVRECREVENUESCHEDULEID = source_sqlazure.REVRECREVENUESCHEDULEID,
REVRECCONTRACTSTARTDATE = source_sqlazure.REVRECCONTRACTSTARTDATE,
REVRECCONTRACTENDDATE = source_sqlazure.REVRECCONTRACTENDDATE,
REVRECSALESDELIVERNOW = source_sqlazure.REVRECSALESDELIVERNOW,
ITEMSERIALNUMBER = source_sqlazure.ITEMSERIALNUMBER,
RETAILCALCULATEDLINEDISCOUNTAMOUNT = source_sqlazure.RETAILCALCULATEDLINEDISCOUNTAMOUNT,
RETAILCALCULATEDLINEDISCOUNTPERCENTAGE = source_sqlazure.RETAILCALCULATEDLINEDISCOUNTPERCENTAGE,
RETAILCALCULATEDMANUALLINEDISCOUNTAMOUNT = source_sqlazure.RETAILCALCULATEDMANUALLINEDISCOUNTAMOUNT,
RETAILCALCULATEDMANUALLINEDISCOUNTPERCENTAGE = source_sqlazure.RETAILCALCULATEDMANUALLINEDISCOUNTPERCENTAGE,
RETAILCALCULATEDPERIODICDISCOUNTAMOUNT = source_sqlazure.RETAILCALCULATEDPERIODICDISCOUNTAMOUNT,
RETAILCALCULATEDPERIODICDISCOUNTPERCENTAGE = source_sqlazure.RETAILCALCULATEDPERIODICDISCOUNTPERCENTAGE,
RETAILCALCULATEDTOTALDISCOUNTAMOUNT = source_sqlazure.RETAILCALCULATEDTOTALDISCOUNTAMOUNT,
RETAILCALCULATEDTOTALDISCOUNTPERCENTAGE = source_sqlazure.RETAILCALCULATEDTOTALDISCOUNTPERCENTAGE,
RETAILCALCULATEDTENDERDISCOUNTAMOUNT = source_sqlazure.RETAILCALCULATEDTENDERDISCOUNTAMOUNT,
RETAILCALCULATEDTENDERDISCOUNTPERCENTAGE = source_sqlazure.RETAILCALCULATEDTENDERDISCOUNTPERCENTAGE,
FULFILLMENTSTATUS = source_sqlazure.FULFILLMENTSTATUS,
FULFILLMENTSTOREID = source_sqlazure.FULFILLMENTSTOREID,
PRODUCTVERSIONID = source_sqlazure.PRODUCTVERSIONID,
DATAAREAID = source_sqlazure.DATAAREAID,
SYNCSTARTDATETIME = source_sqlazure.SYNCSTARTDATETIME
WHEN NOT MATCHED THEN
INSERT (
DEFAULTLEDGERDIMENSIONDISPLAYVALUE,
MAINACCOUNTIDDISPLAYVALUE,
ISDELIVERYADDRESSORDERSPECIFIC,
ORDERLINEREFERENCE,
DEFINITIONGROUP,
EXECUTIONID,
ISSELECTED,
TRANSFERSTATUS,
SALESORDERNUMBER,
INVENTORYLOTID,
SALESORDERLINESTATUS,
ISLINESTOPPED,
CUSTOMERSLINENUMBER,
CUSTOMSDOCUMENTDATE,
CUSTOMSDOCUMENTNUMBER,
CUSTOMSDOCUMENTNAME,
SALESORDERPROMISINGMETHOD,
DELIVERYADDRESSNAME,
DELIVERYMODECODE,
DELIVERYTERMSID,
EXTERNALITEMNUMBER,
BOMID,
ITEMNUMBER,
ROUTEID,
LINEAMOUNT,
LINEDISCOUNTAMOUNT,
LINEDISCOUNTPERCENTAGE,
MULTILINEDISCOUNTAMOUNT,
MULTILINEDISCOUNTPERCENTAGE,
LINEDESCRIPTION,
ALLOWEDOVERDELIVERYPERCENTAGE,
PACKINGUNITSYMBOL,
WILLAUTOMATICINVENTORYRESERVATIONCONSIDERBATCHATTRIBUTES,
WILLREBATECALCULATIONEXCLUDELINE,
SALESREBATEPRODUCTGROUPID,
INTRASTATPORTID,
SALESPRICEQUANTITY,
EINVOICEPROPERTYNUMBER,
CUSTOMERREQUISITIONNUMBER,
CONFIRMEDRECEIPTDATE,
REQUESTEDRECEIPTDATE,
INVENTORYRESERVATIONMETHOD,
COMMISSIONSALESREPRESENTATIVEGROUPID,
FIXEDPRICECHARGES,
SALESPRICE,
ORDEREDSALESQUANTITY,
SALESUNITSYMBOL,
SERVICEORDERNUMBER,
CONFIRMEDSHIPPINGDATE,
REQUESTEDSHIPPINGDATE,
INTRASTATSTATISTICSPROCEDURECODE,
ISINTRASTATTRIANGULARDEAL,
SALESTAXGROUPCODE,
SALESTAXITEMGROUPCODE,
INTRASTATTRANSACTIONCODE,
INTRASTATTRANSPORTMODECODE,
ALLOWEDUNDERDELIVERYPERCENTAGE,
SALESPRODUCTCATEGORYNAME,
NGPCODE,
PRODUCTCONFIGURATIONID,
ITEMBATCHNUMBER,
PRODUCTCOLORID,
SHIPPINGWAREHOUSEID,
SHIPPINGSITEID,
PRODUCTSIZEID,
ORDEREDINVENTORYSTATUSID,
PRODUCTSTYLEID,
FORMATTEDDELVERYADDRESS,
DELIVERYBUILDINGCOMPLIMENT,
DELIVERYADDRESSCITY,
DELIVERYADDRESSCOUNTRYREGIONID,
DELIVERYADDRESSCOUNTRYREGIONISOCODE,
DELIVERYADDRESSCOUNTYID,
DELIVERYADDRESSDESCRIPTION,
DELIVERYADDRESSDISTRICTNAME,
DELIVERYADDRESSDUNSNUMBER,
ISDELIVERYADDRESSPRIVATE,
DELIVERYADDRESSLATITUDE,
DELIVERYADDRESSLOCATIONID,
DELIVERYADDRESSLONGITUDE,
DELIVERYADDRESSPOSTBOX,
DELIVERYADDRESSSTATEID,
DELIVERYADDRESSSTREET,
DELIVERYADDRESSSTREETNUMBER,
DELIVERYADDRESSZIPCODE,
DELIVERYADDRESSCITYINKANA,
DELIVERYADDRESSSTREETINKANA,
DELIVERYADDRESSTIMEZONE,
INTRASTATCOMMODITYCODE,
EINVOICEDIMENSIONACCOUNTCODE,
WITHHOLDINGTAXGROUPCODE,
ITEMWITHHOLDINGTAXCODEGROUPCODE,
CREDITNOTEREASONCODE,
INTRASTATSTATISTICVALUE,
INTRASTATSPECIALMOVEMENTCODE,
DELIVERYSALESTAXGROUPCODE,
DELIVERYSALESTAXITEMGROUPCODE,
DELIVERYCFOPCODE,
CFOPCODE,
SUFRAMADISCOUNTPERCENTAGE,
SERVICEFISCALINFORMATIONCODE,
FISCALDOCUMENTTYPEID,
LINECREATIONSEQUENCENUMBER,
GIFTCARDBUYEREMAIL,
GIFTCARDBUYERNAME,
GIFTCARDGIFTMESSAGE,
GIFTCARDNUMBER,
GIFTCARDRECIPIENTEMAIL,
GIFTCARDRECIPIENTNAME,
GIFTCARDTYPE,
SKIPCREATEAUTOCHARGES,
PARTITION,
ORDEREDCATCHWEIGHTQUANTITY,
PROJECTCATEGORYID,
PROJECTID,
PROJECTLINEPROPERTYID,
CURRENCYCODE,
SHIPPINGWAREHOUSELOCATIONID,
REVRECREVENUESCHEDULEID,
REVRECCONTRACTSTARTDATE,
REVRECCONTRACTENDDATE,
REVRECSALESDELIVERNOW,
ITEMSERIALNUMBER,
RETAILCALCULATEDLINEDISCOUNTAMOUNT,
RETAILCALCULATEDLINEDISCOUNTPERCENTAGE,
RETAILCALCULATEDMANUALLINEDISCOUNTAMOUNT,
RETAILCALCULATEDMANUALLINEDISCOUNTPERCENTAGE,
RETAILCALCULATEDPERIODICDISCOUNTAMOUNT,
RETAILCALCULATEDPERIODICDISCOUNTPERCENTAGE,
RETAILCALCULATEDTOTALDISCOUNTAMOUNT,
RETAILCALCULATEDTOTALDISCOUNTPERCENTAGE,
RETAILCALCULATEDTENDERDISCOUNTAMOUNT,
RETAILCALCULATEDTENDERDISCOUNTPERCENTAGE,
FULFILLMENTSTATUS,
FULFILLMENTSTOREID,
PRODUCTVERSIONID,
DATAAREAID,
SYNCSTARTDATETIME
)
VALUES (
source_sqlazure.DEFAULTLEDGERDIMENSIONDISPLAYVALUE,
source_sqlazure.MAINACCOUNTIDDISPLAYVALUE,
source_sqlazure.ISDELIVERYADDRESSORDERSPECIFIC,
source_sqlazure.ORDERLINEREFERENCE,
source_sqlazure.DEFINITIONGROUP,
source_sqlazure.EXECUTIONID,
source_sqlazure.ISSELECTED,
source_sqlazure.TRANSFERSTATUS,
source_sqlazure.SALESORDERNUMBER,
source_sqlazure.INVENTORYLOTID,
source_sqlazure.SALESORDERLINESTATUS,
source_sqlazure.ISLINESTOPPED,
source_sqlazure.CUSTOMERSLINENUMBER,
source_sqlazure.CUSTOMSDOCUMENTDATE,
source_sqlazure.CUSTOMSDOCUMENTNUMBER,
source_sqlazure.CUSTOMSDOCUMENTNAME,
source_sqlazure.SALESORDERPROMISINGMETHOD,
source_sqlazure.DELIVERYADDRESSNAME,
source_sqlazure.DELIVERYMODECODE,
source_sqlazure.DELIVERYTERMSID,
source_sqlazure.EXTERNALITEMNUMBER,
source_sqlazure.BOMID,
source_sqlazure.ITEMNUMBER,
source_sqlazure.ROUTEID,
source_sqlazure.LINEAMOUNT,
source_sqlazure.LINEDISCOUNTAMOUNT,
source_sqlazure.LINEDISCOUNTPERCENTAGE,
source_sqlazure.MULTILINEDISCOUNTAMOUNT,
source_sqlazure.MULTILINEDISCOUNTPERCENTAGE,
source_sqlazure.LINEDESCRIPTION,
source_sqlazure.ALLOWEDOVERDELIVERYPERCENTAGE,
source_sqlazure.PACKINGUNITSYMBOL,
source_sqlazure.WILLAUTOMATICINVENTORYRESERVATIONCONSIDERBATCHATTRIBUTES,
source_sqlazure.WILLREBATECALCULATIONEXCLUDELINE,
source_sqlazure.SALESREBATEPRODUCTGROUPID,
source_sqlazure.INTRASTATPORTID,
source_sqlazure.SALESPRICEQUANTITY,
source_sqlazure.EINVOICEPROPERTYNUMBER,
source_sqlazure.CUSTOMERREQUISITIONNUMBER,
source_sqlazure.CONFIRMEDRECEIPTDATE,
source_sqlazure.REQUESTEDRECEIPTDATE,
source_sqlazure.INVENTORYRESERVATIONMETHOD,
source_sqlazure.COMMISSIONSALESREPRESENTATIVEGROUPID,
source_sqlazure.FIXEDPRICECHARGES,
source_sqlazure.SALESPRICE,
source_sqlazure.ORDEREDSALESQUANTITY,
source_sqlazure.SALESUNITSYMBOL,
source_sqlazure.SERVICEORDERNUMBER,
source_sqlazure.CONFIRMEDSHIPPINGDATE,
source_sqlazure.REQUESTEDSHIPPINGDATE,
source_sqlazure.INTRASTATSTATISTICSPROCEDURECODE,
source_sqlazure.ISINTRASTATTRIANGULARDEAL,
source_sqlazure.SALESTAXGROUPCODE,
source_sqlazure.SALESTAXITEMGROUPCODE,
source_sqlazure.INTRASTATTRANSACTIONCODE,
source_sqlazure.INTRASTATTRANSPORTMODECODE,
source_sqlazure.ALLOWEDUNDERDELIVERYPERCENTAGE,
source_sqlazure.SALESPRODUCTCATEGORYNAME,
source_sqlazure.NGPCODE,
source_sqlazure.PRODUCTCONFIGURATIONID,
source_sqlazure.ITEMBATCHNUMBER,
source_sqlazure.PRODUCTCOLORID,
source_sqlazure.SHIPPINGWAREHOUSEID,
source_sqlazure.SHIPPINGSITEID,
source_sqlazure.PRODUCTSIZEID,
source_sqlazure.ORDEREDINVENTORYSTATUSID,
source_sqlazure.PRODUCTSTYLEID,
source_sqlazure.FORMATTEDDELVERYADDRESS,
source_sqlazure.DELIVERYBUILDINGCOMPLIMENT,
source_sqlazure.DELIVERYADDRESSCITY,
source_sqlazure.DELIVERYADDRESSCOUNTRYREGIONID,
source_sqlazure.DELIVERYADDRESSCOUNTRYREGIONISOCODE,
source_sqlazure.DELIVERYADDRESSCOUNTYID,
source_sqlazure.DELIVERYADDRESSDESCRIPTION,
source_sqlazure.DELIVERYADDRESSDISTRICTNAME,
source_sqlazure.DELIVERYADDRESSDUNSNUMBER,
source_sqlazure.ISDELIVERYADDRESSPRIVATE,
source_sqlazure.DELIVERYADDRESSLATITUDE,
source_sqlazure.DELIVERYADDRESSLOCATIONID,
source_sqlazure.DELIVERYADDRESSLONGITUDE,
source_sqlazure.DELIVERYADDRESSPOSTBOX,
source_sqlazure.DELIVERYADDRESSSTATEID,
source_sqlazure.DELIVERYADDRESSSTREET,
source_sqlazure.DELIVERYADDRESSSTREETNUMBER,
source_sqlazure.DELIVERYADDRESSZIPCODE,
source_sqlazure.DELIVERYADDRESSCITYINKANA,
source_sqlazure.DELIVERYADDRESSSTREETINKANA,
source_sqlazure.DELIVERYADDRESSTIMEZONE,
source_sqlazure.INTRASTATCOMMODITYCODE,
source_sqlazure.EINVOICEDIMENSIONACCOUNTCODE,
source_sqlazure.WITHHOLDINGTAXGROUPCODE,
source_sqlazure.ITEMWITHHOLDINGTAXCODEGROUPCODE,
source_sqlazure.CREDITNOTEREASONCODE,
source_sqlazure.INTRASTATSTATISTICVALUE,
source_sqlazure.INTRASTATSPECIALMOVEMENTCODE,
source_sqlazure.DELIVERYSALESTAXGROUPCODE,
source_sqlazure.DELIVERYSALESTAXITEMGROUPCODE,
source_sqlazure.DELIVERYCFOPCODE,
source_sqlazure.CFOPCODE,
source_sqlazure.SUFRAMADISCOUNTPERCENTAGE,
source_sqlazure.SERVICEFISCALINFORMATIONCODE,
source_sqlazure.FISCALDOCUMENTTYPEID,
source_sqlazure.LINECREATIONSEQUENCENUMBER,
source_sqlazure.GIFTCARDBUYEREMAIL,
source_sqlazure.GIFTCARDBUYERNAME,
source_sqlazure.GIFTCARDGIFTMESSAGE,
source_sqlazure.GIFTCARDNUMBER,
source_sqlazure.GIFTCARDRECIPIENTEMAIL,
source_sqlazure.GIFTCARDRECIPIENTNAME,
source_sqlazure.GIFTCARDTYPE,
source_sqlazure.SKIPCREATEAUTOCHARGES,
source_sqlazure.PARTITION,
source_sqlazure.ORDEREDCATCHWEIGHTQUANTITY,
source_sqlazure.PROJECTCATEGORYID,
source_sqlazure.PROJECTID,
source_sqlazure.PROJECTLINEPROPERTYID,
source_sqlazure.CURRENCYCODE,
source_sqlazure.SHIPPINGWAREHOUSELOCATIONID,
source_sqlazure.REVRECREVENUESCHEDULEID,
source_sqlazure.REVRECCONTRACTSTARTDATE,
source_sqlazure.REVRECCONTRACTENDDATE,
source_sqlazure.REVRECSALESDELIVERNOW,
source_sqlazure.ITEMSERIALNUMBER,
source_sqlazure.RETAILCALCULATEDLINEDISCOUNTAMOUNT,
source_sqlazure.RETAILCALCULATEDLINEDISCOUNTPERCENTAGE,
source_sqlazure.RETAILCALCULATEDMANUALLINEDISCOUNTAMOUNT,
source_sqlazure.RETAILCALCULATEDMANUALLINEDISCOUNTPERCENTAGE,
source_sqlazure.RETAILCALCULATEDPERIODICDISCOUNTAMOUNT,
source_sqlazure.RETAILCALCULATEDPERIODICDISCOUNTPERCENTAGE,
source_sqlazure.RETAILCALCULATEDTOTALDISCOUNTAMOUNT,
source_sqlazure.RETAILCALCULATEDTOTALDISCOUNTPERCENTAGE,
source_sqlazure.RETAILCALCULATEDTENDERDISCOUNTAMOUNT,
source_sqlazure.RETAILCALCULATEDTENDERDISCOUNTPERCENTAGE,
source_sqlazure.FULFILLMENTSTATUS,
source_sqlazure.FULFILLMENTSTOREID,
source_sqlazure.PRODUCTVERSIONID,
source_sqlazure.DATAAREAID,
source_sqlazure.SYNCSTARTDATETIME
)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
END
January 20, 2021 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply