Compare two SQL tables and find the differences
<p>I have two table that have more than 30 columns each. I need to compare the tables and find those rows that are</p>

<ol>
<li>Are in one table, but not in the other</li>
<li>have most columns the same, but not all</li>
</ol>

<p>I'm having difficulty getting this done. I've tried using a Union All to compare the two tables. With tables A and B I get all rows that</p>

<ol>
<li>Are in A and not in B</li>
<li>Are in B and not in A</li>
<li>Are in both tables but don't have all columns exactly the same</li>
</ol>

<p>I've used the <code>except</code> function of SQL and I can get all the rows from A that are not in B or all rows from B that are not in A. </p>

<p>I have an SP that does the <code>UNION ALL</code> based on <a href="http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx" rel="nofollow">this</a> Blog article. Then I've found where <code>Except</code> is used to get differences from two tables and have tried that. I am unable to accomplish my goal however. What I'm ultimately trying to do is that I have Data in A that needs to be updated/Deleted/Added to based on what is in table B. I want to be able to compare the 2 table and</p>

<ol>
<li>Insert from B what is not already in A</li>
<li>Delete from A what is not in B</li>
<li>Update A with rows in B that are different from A in only a couple of columns</li>
</ol>

<p>Any help or suggestions would be appreciated.</p>

<p><strong>EDIT 3</strong></p>

<p>After speaking with my team here I have a 4 column match that I can make to identify which rows should be the same. It's the PONumber, POLineNumber, IMLineNumber and ProjectNumber (in that order) that I'll match on since I don't have a common ID.</p>

<p><strong>Edit</strong></p>

<p>There is no primary key that is in both tables. I should have added this at first, but didn't think of it till seeing the comments and answers below. I have to match everything based on the data in the rows and not on a particular key. I will get the tables data maps on here tomorrow.</p>

<p>Here's what I've got so far:</p>

<pre><code>    insert into working.ComparedMaterialTrackingTables(TableName
            ,State
            ,ProjectNumber
            ,SubProjectNumber
            ,PONumber
            ,POLineNumber
            ,IMLineNumber
            ,CalloutDetails
            ,POStatus
            ,BlanketRelease
            ,PODateIssued
            ,DateNeeded
            ,POInvoiceStatus
            ,ItemCode
            ,ItemDesc
            ,QuantityOrdered
            ,QuantityReceived
            ,DateReceived
            ,RecordStatus
            ,ShipToLocation
            ,DeliverToLocation
            ,ReceiverId
            ,TraceNumber
            ,BOL
            ,PackSlipNumber
            ,POTotalAmount
            ,POLineAmount
            ,CostCode
            ,RequisitionId
            ,RequisitionLineNumber
            ,Vendor
            ,ExchangeName
            ,ProjectType
            ,COECurrentProjectStatus
            ,ReadyForService
            ,ScheduledShipDate
            ,MaterialComments
            ,SchedulerComments
            ,MaterialSpecialist)
exec spCompareTables @Table1='TestingTable', @Table2='working.MaterialTracking'
, @T1Columns='State,ProjectNumber,SubProjectNumber,PONumber,POLineNumber,IMLineNumber,CalloutDetails,POStatus,BlanketRelease,PODateIssued,DateNeeded
    ,POInvoiceStatus,ItemCode,ItemDesc,QuantityOrdered,QuantityReceived,DateReceived,RecordStatus,ShipToLocation,DeliverToLocation,ReceiverId
    ,TraceNumber,BOL,PackSlipNumber,POTotalAmount,POLineAmount,CostCode,RequisitionId,RequisitionLineNumber,Vendor,ExchangeName,ProjectType
    ,COECurrentProjectStatus,ReadyForService,ScheduledShipDate,MaterialComments,SchedulerComments,MaterialSpecialist'

begin tran  --commit    rollback
create table #MaterialID(ID int)
insert into #MaterialID(ID)
select tt.woid from working.MaterialTracking tt
join (select State
            ,ProjectNumber
            ,SubProjectNumber
            ,PONumber
            ,POLineNumber
            ,IMLineNumber
            ,CalloutDetails
            ,POStatus
            ,BlanketRelease
            ,PODateIssued
            ,DateNeeded
            ,POInvoiceStatus
            ,ItemCode
            ,ItemDesc
            ,QuantityOrdered
            ,QuantityReceived
            ,DateReceived
            ,RecordStatus
            ,ShipToLocation
            ,DeliverToLocation
            ,ReceiverId
            ,TraceNumber
            ,BOL
            ,PackSlipNumber
            ,POTotalAmount
            ,POLineAmount
            ,CostCode
            ,RequisitionId
            ,RequisitionLineNumber
            ,Vendor
            ,ExchangeName
            ,ProjectType
            ,COECurrentProjectStatus
            ,ReadyForService
            ,ScheduledShipDate
            ,MaterialComments
            ,SchedulerComments
            ,MaterialSpecialist
    from working.MaterialTracking
    Except
    select State
            ,ProjectNumber
            ,SubProjectNumber
            ,PONumber
            ,POLineNumber
            ,IMLineNumber
            ,CalloutDetails
            ,POStatus
            ,BlanketRelease
            ,PODateIssued
            ,DateNeeded
            ,POInvoiceStatus
            ,ItemCode
            ,ItemDesc
            ,QuantityOrdered
            ,QuantityReceived
            ,DateReceived
            ,RecordStatus
            ,ShipToLocation
            ,DeliverToLocation
            ,ReceiverId
            ,TraceNumber
            ,BOL
            ,PackSlipNumber
            ,POTotalAmount
            ,POLineAmount
            ,CostCode
            ,RequisitionId
            ,RequisitionLineNumber
            ,Vendor
            ,ExchangeName
            ,ProjectType
            ,COECurrentProjectStatus
            ,ReadyForService
            ,ScheduledShipDate
            ,MaterialComments
            ,SchedulerComments
            ,MaterialSpecialist
    from TestingTable) ctt on ctt.State = tt.State 
            and ctt.ProjectNumber = tt.ProjectNumber 
            and ctt.SubProjectNumber = tt.SubProjectNumber 
            and ctt.PONumber = tt.PONumber 
            and ctt.POLineNumber = tt.POLineNumber 
            and ctt.IMLineNumber = tt.IMLineNumber 
            and ctt.POStatus = tt.POStatus 
            and ctt.BlanketRelease = tt.BlanketRelease 
            and ctt.PODateIssued = tt.PODateIssued 
            and ctt.DateNeeded = tt.DateNeeded 
            and ctt.POInvoiceStatus = tt.POInvoiceStatus 
            and ctt.ItemCode = tt.ItemCode 
            and ctt.ItemDesc = tt.ItemDesc 
            and ctt.QuantityOrdered = tt.QuantityOrdered 
            and ctt.ShipToLocation = tt.ShipToLocation 
            and ctt.DeliverToLocation = tt.DeliverToLocation 
            and ctt.TraceNumber = tt.TraceNumber 
            and ctt.BOL = tt.BOL 
            and ctt.PackSlipNumber = tt.PackSlipNumber 
            and ctt.POTotalAmount = tt.POTotalAmount 
            and ctt.POLineAmount = tt.POLineAmount 
            and ctt.CostCode = tt.CostCode 
            and ctt.RequisitionId = tt.RequisitionId 
            and ctt.RequisitionLineNumber = tt.RequisitionLineNumber 
            and ctt.Vendor = tt.Vendor 
            and ctt.ExchangeName = tt.ExchangeName 
            and ctt.COECurrentProjectStatus = tt.COECurrentProjectStatus 
            and ctt.ReadyForService = tt.ReadyForService 
            and ctt.MaterialSpecialist = tt.MaterialSpecialist 
            and ctt.QuantityReceived = tt.QuantityReceived 
            and ctt.DateReceived = tt.DateReceived 
            and ctt.ReceiverId = tt.ReceiverId 
            and ctt.ScheduledShipDate = tt.ScheduledShipDate 
            and ctt.CalloutDetails = tt.CalloutDetails 
            and ctt.ProjectType = tt.ProjectType 
            and ctt.RecordStatus = tt.RecordStatus 
            and ctt.MaterialComments = tt.MaterialComments 
            and ctt.SchedulerComments = tt.SchedulerComments

insert into TestingTable (State
            ,ProjectNumber
            ,SubProjectNumber
            ,PONumber
            ,POLineNumber
            ,IMLineNumber
            ,CalloutDetails
            ,POStatus
            ,BlanketRelease
            ,PODateIssued
            ,DateNeeded
            ,POInvoiceStatus
            ,ItemCode
            ,ItemDesc
            ,QuantityOrdered
            ,QuantityReceived
            ,DateReceived
            ,RecordStatus
            ,ShipToLocation
            ,DeliverToLocation
            ,ReceiverId
            ,TraceNumber
            ,BOL
            ,PackSlipNumber
            ,POTotalAmount
            ,POLineAmount
            ,CostCode
            ,RequisitionId
            ,RequisitionLineNumber
            ,Vendor
            ,ExchangeName
            ,ProjectType
            ,COECurrentProjectStatus
            ,ReadyForService
            ,ScheduledShipDate
            ,MaterialComments
            ,SchedulerComments
            ,MaterialSpecialist)
select State
        ,ProjectNumber
        ,SubProjectNumber
        ,PONumber
        ,POLineNumber
        ,IMLineNumber
        ,CalloutDetails
        ,POStatus
        ,BlanketRelease
        ,PODateIssued
        ,DateNeeded
        ,POInvoiceStatus
        ,ItemCode
        ,ItemDesc
        ,QuantityOrdered
        ,QuantityReceived
        ,DateReceived
        ,RecordStatus
        ,ShipToLocation
        ,DeliverToLocation
        ,ReceiverId
        ,TraceNumber
        ,BOL
        ,PackSlipNumber
        ,POTotalAmount
        ,POLineAmount
        ,CostCode
        ,RequisitionId
        ,RequisitionLineNumber
        ,Vendor
        ,ExchangeName
        ,ProjectType
        ,COECurrentProjectStatus
        ,ReadyForService
        ,ScheduledShipDate
        ,MaterialComments
        ,SchedulerComments
        ,MaterialSpecialist
from working.MaterialTracking
Except
select State
        ,ProjectNumber
        ,SubProjectNumber
        ,PONumber
        ,POLineNumber
        ,IMLineNumber
        ,CalloutDetails
        ,POStatus
        ,BlanketRelease
        ,PODateIssued
        ,DateNeeded
        ,POInvoiceStatus
        ,ItemCode
        ,ItemDesc
        ,QuantityOrdered
        ,QuantityReceived
        ,DateReceived
        ,RecordStatus
        ,ShipToLocation
        ,DeliverToLocation
        ,ReceiverId
        ,TraceNumber
        ,BOL
        ,PackSlipNumber
        ,POTotalAmount
        ,POLineAmount
        ,CostCode
        ,RequisitionId
        ,RequisitionLineNumber
        ,Vendor
        ,ExchangeName
        ,ProjectType
        ,COECurrentProjectStatus
        ,ReadyForService
        ,ScheduledShipDate
        ,MaterialComments
        ,SchedulerComments
        ,MaterialSpecialist
from TestingTable

delete from working.MaterialTracking
from working.MaterialTracking mt
join #MaterialID mi on mi.ID = mt.WOID
</code></pre>

<p><strong>Edit 2</strong>
Here's the table data for the working.MaterialTracking table:</p>

<pre><code>TABLE_NAME          COLUMN_NAME             IS_NULLABLE DATA_TYPE
MaterialTracking    ID                      NO          uniqueidentifier
MaterialTracking    State                   YES         nvarchar
MaterialTracking    ProjectNumber           YES         nvarchar
MaterialTracking    SubProjectNumber        YES         nvarchar
MaterialTracking    PONumber                YES         nvarchar
MaterialTracking    POLineNumber            YES         int
MaterialTracking    IMLineNumber            YES         nvarchar
MaterialTracking    CalloutDetails          YES         nvarchar
MaterialTracking    POStatus                YES         nvarchar
MaterialTracking    BlanketRelease          YES         int
MaterialTracking    PODateIssued            YES         datetime
MaterialTracking    DateNeeded              YES         datetime
MaterialTracking    POInvoiceStatus         YES         nvarchar
MaterialTracking    ItemCode                YES         nvarchar
MaterialTracking    ItemDesc                YES         nvarchar
MaterialTracking    QuantityOrdered         YES         numeric
MaterialTracking    QuantityReceived        YES         numeric
MaterialTracking    DateReceived            YES         nvarchar
MaterialTracking    RecordStatus            YES         varchar
MaterialTracking    ShipToLocation          YES         nvarchar
MaterialTracking    DeliverToLocation       YES         nvarchar
MaterialTracking    ReceiverId              YES         nvarchar
MaterialTracking    TraceNumber             YES         nvarchar
MaterialTracking    BOL                     YES         nvarchar
MaterialTracking    PackSlipNumber          YES         nvarchar
MaterialTracking    POTotalAmount           YES         numeric
MaterialTracking    POLineAmount            YES         numeric
MaterialTracking    CostCode                YES         nvarchar
MaterialTracking    RequisitionId           YES         nvarchar
MaterialTracking    RequisitionLineNumber   YES         int
MaterialTracking    Vendor                  YES         nvarchar
MaterialTracking    ExchangeName            YES         nvarchar
MaterialTracking    ProjectType             YES         nvarchar
MaterialTracking    COECurrentProjectStatus YES         nvarchar
MaterialTracking    ReadyForService         YES         datetime
MaterialTracking    ScheduledShipDate       YES         nvarchar
MaterialTracking    MaterialComments        YES         nvarchar
MaterialTracking    SchedulerComments       YES         nvarchar
MaterialTracking    MaterialSpecialist      YES         nvarchar
MaterialTracking    woid                    NO          int
</code></pre>

<p>And here's the TestingTable:</p>

<pre><code>TABLE_NAME      COLUMN_NAME             IS_NULLABLE DATA_TYPE
TestingTable    State                   YES         nvarchar
TestingTable    ProjectNumber           YES         nvarchar
TestingTable    SubProjectNumber        YES         nvarchar
TestingTable    PONumber                YES         nvarchar
TestingTable    POLineNumber            YES         int
TestingTable    IMLineNumber            YES         nvarchar
TestingTable    POStatus                YES         nvarchar
TestingTable    BlanketRelease          YES         int
TestingTable    PODateIssued            YES         datetime
TestingTable    DateNeeded              YES         datetime
TestingTable    POInvoiceStatus         YES         nvarchar
TestingTable    ItemCode                YES         nvarchar
TestingTable    ItemDesc                YES         nvarchar
TestingTable    QuantityOrdered         YES         numeric
TestingTable    ShipToLocation          YES         nvarchar
TestingTable    DeliverToLocation       YES         nvarchar
TestingTable    TraceNumber             YES         nvarchar
TestingTable    BOL                     YES         nvarchar
TestingTable    PackSlipNumber          YES         nvarchar
TestingTable    POTotalAmount           YES         numeric
TestingTable    POLineAmount            YES         numeric
TestingTable    CostCode                YES         nvarchar
TestingTable    RequisitionId           YES         nvarchar
TestingTable    RequisitionLineNumber   YES         int
TestingTable    Vendor                  YES         nvarchar
TestingTable    ExchangeName            YES         nvarchar
TestingTable    COECurrentProjectStatus YES         nvarchar
TestingTable    ReadyForService         YES         datetime
TestingTable    MaterialSpecialist      YES         nvarchar
TestingTable    QuantityReceived        YES         numeric
TestingTable    DateReceived            YES         nvarchar
TestingTable    ReceiverId              YES         nvarchar
TestingTable    ScheduledShipDate       YES         datetime
TestingTable    CalloutDetails          YES         nvarchar
TestingTable    ProjectType             YES         nvarchar
TestingTable    RecordStatus            YES         nvarchar
TestingTable    MaterialComments        YES         nvarchar
TestingTable    SchedulerComments       YES         nvarchar
TestingTable    ID                      YES         int
</code></pre>

<p>The ID for both columns is an identity that is created as the rows are added and is not dependent on each other. So trying to match based on the ID won't work. I'm not able to do a truncate and re-populate from B since there are people in the table working and locking rows. That's why I'm just updating, inserting and deleting rows not the whole table. I do want A to be exactly B I just can't truncate and replace.</p>

<p><strong>EDIT 4</strong></p>

<p>Here is the <code>merge</code> script that I created and ran. It seems to have worked, but it deleted each row and then inserted each row. I don't know if that is what is supposed to happen or if I set it up wrong?</p>

<pre><code>    begin tran  --commit    rollback
merge into pmdb.MaterialTracking as target
using (select State,ProjectNumber,SubProjectNumber,PONumber,POLineNumber,IMLineNumber,CalloutDetails,POStatus,BlanketRelease,PODateIssued,DateNeeded
        ,POInvoiceStatus,ItemCode,ItemDesc,QuantityOrdered,QuantityReceived,DateReceived,RecordStatus,ShipToLocation,DeliverToLocation,ReceiverId
        ,TraceNumber,BOL,PackSlipNumber,POTotalAmount,POLineAmount,CostCode,RequisitionId,RequisitionLineNumber,Vendor,ExchangeName,ProjectType
        ,COECurrentProjectStatus,ReadyForService,ScheduledShipDate,MaterialComments,SchedulerComments,MaterialSpecialist from working.MaterialTracking) as source 
    on (target.PONumber = source.PONumber and target.POLineNumber = source.POLineNumber and target.IMLineNumber = source.IMLineNumber 
            and target.ProjectNumber = source.ProjectNumber)
when matched and (ISNULL(Target.State,'NULL') &lt;&gt; ISNULL(Source.State,'NULL') OR
                    ISNULL(Target.SubProjectNumber,'NULL') &lt;&gt; ISNULL(Source.SubProjectNumber,'NULL') OR
                    ISNULL(Target.POStatus,'NULL') &lt;&gt; ISNULL(Source.POStatus,'NULL') OR
                    ISNULL(Target.BlanketRelease,'NULL') &lt;&gt; ISNULL(Source.BlanketRelease,'NULL') OR
                    ISNULL(Target.PODateIssued,'NULL') &lt;&gt; ISNULL(Source.PODateIssued,'NULL') OR
                    ISNULL(Target.DateNeeded,'NULL') &lt;&gt; ISNULL(Source.DateNeeded,'NULL') OR
                    ISNULL(Target.POInvoiceStatus,'NULL') &lt;&gt; ISNULL(Source.POInvoiceStatus,'NULL') OR
                    ISNULL(Target.ItemCode,'NULL') &lt;&gt; ISNULL(Source.ItemCode,'NULL') OR
                    ISNULL(Target.ItemDesc,'NULL') &lt;&gt; ISNULL(Source.ItemDesc,'NULL') OR
                    ISNULL(Target.QuantityOrdered,'NULL') &lt;&gt; ISNULL(Source.QuantityOrdered,'NULL') OR
                    ISNULL(Target.ShipToLocation,'NULL') &lt;&gt; ISNULL(Source.ShipToLocation,'NULL') OR
                    ISNULL(Target.DeliverToLocation,'NULL') &lt;&gt; ISNULL(Source.DeliverToLocation,'NULL') OR
                    ISNULL(Target.TraceNumber,'NULL') &lt;&gt; ISNULL(Source.TraceNumber,'NULL') OR
                    ISNULL(Target.BOL,'NULL') &lt;&gt; ISNULL(Source.BOL,'NULL') OR
                    ISNULL(Target.PackSlipNumber,'NULL') &lt;&gt; ISNULL(Source.PackSlipNumber,'NULL') OR
                    ISNULL(Target.POTotalAmount,'NULL') &lt;&gt; ISNULL(Source.POTotalAmount,'NULL') OR
                    ISNULL(Target.POLineAmount,'NULL') &lt;&gt; ISNULL(Source.POLineAmount,'NULL') OR
                    ISNULL(Target.CostCode,'NULL') &lt;&gt; ISNULL(Source.CostCode,'NULL') OR
                    ISNULL(Target.RequisitionId,'NULL') &lt;&gt; ISNULL(Source.RequisitionId,'NULL') OR
                    ISNULL(Target.RequisitionLineNumber,'NULL') &lt;&gt; ISNULL(Source.RequisitionLineNumber,'NULL') OR
                    ISNULL(Target.Vendor,'NULL') &lt;&gt; ISNULL(Source.Vendor,'NULL') OR
                    ISNULL(Target.ExchangeName,'NULL') &lt;&gt; ISNULL(Source.ExchangeName,'NULL') OR
                    ISNULL(Target.COECurrentProjectStatus,'NULL') &lt;&gt; ISNULL(Source.COECurrentProjectStatus,'NULL') OR
                    ISNULL(Target.ReadyForService,'NULL') &lt;&gt; ISNULL(Source.ReadyForService,'NULL') OR
                    ISNULL(Target.MaterialSpecialist,'NULL') &lt;&gt; ISNULL(Source.MaterialSpecialist,'NULL') OR
                    ISNULL(Target.QuantityReceived,'NULL') &lt;&gt; ISNULL(Source.QuantityReceived,'NULL') OR
                    ISNULL(Target.DateReceived,'NULL') &lt;&gt; ISNULL(Source.DateReceived,'NULL') OR
                    ISNULL(Target.ReceiverId,'NULL') &lt;&gt; ISNULL(Source.ReceiverId,'NULL') OR
                    ISNULL(Target.ScheduledShipDate,'NULL') &lt;&gt; ISNULL(Source.ScheduledShipDate,'NULL') OR
                    ISNULL(Target.CalloutDetails,'NULL') &lt;&gt; ISNULL(Source.CalloutDetails,'NULL') OR
                    ISNULL(Target.ProjectType,'NULL') &lt;&gt; ISNULL(Source.ProjectType,'NULL') OR
                    ISNULL(Target.RecordStatus,'NULL') &lt;&gt; ISNULL(Source.RecordStatus,'NULL') OR
                    ISNULL(Target.MaterialComments,'NULL') &lt;&gt; ISNULL(Source.MaterialComments,'NULL') OR
                    ISNULL(Target.SchedulerComments,'NULL') &lt;&gt; ISNULL(Source.SchedulerComments,'NULL'))
    then update
    set State = Source.State,
        SubProjectNumber = Source.SubProjectNumber,
        POStatus = Source.POStatus,
        BlanketRelease = Source.BlanketRelease,
        PODateIssued = Source.PODateIssued,
        DateNeeded = Source.DateNeeded,
        POInvoiceStatus = Source.POInvoiceStatus,
        ItemCode = Source.ItemCode,
        ItemDesc = Source.ItemDesc,
        QuantityOrdered = Source.QuantityOrdered,
        ShipToLocation = Source.ShipToLocation,
        DeliverToLocation = Source.DeliverToLocation,
        TraceNumber = Source.TraceNumber,
        BOL = Source.BOL,
        PackSlipNumber = Source.PackSlipNumber,
        POTotalAmount = Source.POTotalAmount,
        POLineAmount = Source.POLineAmount,
        CostCode = Source.CostCode,
        RequisitionId = Source.RequisitionId,
        RequisitionLineNumber = Source.RequisitionLineNumber,
        Vendor = Source.Vendor,
        ExchangeName = Source.ExchangeName,
        COECurrentProjectStatus = Source.COECurrentProjectStatus,
        ReadyForService = Source.ReadyForService,
        MaterialSpecialist = Source.MaterialSpecialist,
        QuantityReceived = Source.QuantityReceived,
        DateReceived = Source.DateReceived,
        ReceiverId = Source.ReceiverId,
        ScheduledShipDate = Source.ScheduledShipDate,
        CalloutDetails = Source.CalloutDetails,
        ProjectType = Source.ProjectType,
        RecordStatus = Source.RecordStatus,
        MaterialComments = Source.MaterialComments,
        SchedulerComments = Source.SchedulerComments

when not matched by target then
insert (State,ProjectNumber,SubProjectNumber,PONumber,POLineNumber,IMLineNumber,CalloutDetails,POStatus,BlanketRelease,PODateIssued,DateNeeded
        ,POInvoiceStatus,ItemCode,ItemDesc,QuantityOrdered,QuantityReceived,DateReceived,RecordStatus,ShipToLocation,DeliverToLocation,ReceiverId
        ,TraceNumber,BOL,PackSlipNumber,POTotalAmount,POLineAmount,CostCode,RequisitionId,RequisitionLineNumber,Vendor,ExchangeName,ProjectType
        ,COECurrentProjectStatus,ReadyForService,ScheduledShipDate,MaterialComments,SchedulerComments,MaterialSpecialist)
values (State,ProjectNumber,SubProjectNumber,PONumber,POLineNumber,IMLineNumber,CalloutDetails,POStatus,BlanketRelease,PODateIssued,DateNeeded
        ,POInvoiceStatus,ItemCode,ItemDesc,QuantityOrdered,QuantityReceived,DateReceived,RecordStatus,ShipToLocation,DeliverToLocation,ReceiverId
        ,TraceNumber,BOL,PackSlipNumber,POTotalAmount,POLineAmount,CostCode,RequisitionId,RequisitionLineNumber,Vendor,ExchangeName,ProjectType
        ,COECurrentProjectStatus,ReadyForService,ScheduledShipDate,MaterialComments,SchedulerComments,MaterialSpecialist)

when not matched by source then
delete

output $action, Inserted.*,Deleted.*;
/*
select * from pmdb.MaterialTracking
*/
</code></pre>