1

How do I conduct a Except statement and Add in one more column? Three tables with the exact same structure. (CustomerTransaction1,CustomerTransaction2, CustomerTransactionDiff)

I want the LoadDate from CustomerTransaction1. If not, I will have to rejoin on the primary key, and extract the load date.

I do not want to except/compare on the LoadDate, just add in the column.

Create table dbo.CustomerTransaction1
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
)


insert into CustomerTransactionDiff
( CustomerTransactionId, PurchasePrice,Quantity,Loaddate)
select 
    CustomerTransactionId,
    CustomerName,
    PurchasePrice,
    Quantity
from dbo.CustomerTransaction
except
select 
    CustomerTransactionId,
    CustomerName,
    PurchasePrice,
    Quantity
from dbo.CustomerTransaction2
  • 3
    Use NOT EXISTS instead. – Erik Darling Oct 25 '18 at 18:20
4

Here is a sample solution (based on sp_BlitzErik's comment):

--demo setup
drop table if exists dbo.CustomerTransaction1;
drop table if exists dbo.CustomerTransaction2;
drop table if exists dbo.CustomerTransactionDiff;
Create table dbo.CustomerTransaction1
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);

insert into dbo.CustomerTransaction1(CustomerTransactionId,CustomerName,PurchasePrice,Quantity,Loaddate) values
(1,'Fred',100,5,'2018-10-25'),
(2,'Bob',200,1,'2018-10-25')

Create table dbo.CustomerTransaction2
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);
insert into dbo.CustomerTransaction2(CustomerTransactionId,CustomerName,PurchasePrice,Quantity,Loaddate) values
(2,'Bob',200,1,'2018-10-25')

Create table dbo.CustomerTransactionDiff
(
    CustomerTransactionId int primary key,
    CustomerName varchar(255),
    PurchasePrice decimal(10,2),
    Quantity int,
    Loaddate datetime
);

--The solution
INSERT INTO CustomerTransactionDiff (
    c1.CustomerTransactionId
    ,CustomerName
    ,c1.PurchasePrice
    ,c1.Quantity
    ,c1.Loaddate
    )
SELECT c1.CustomerTransactionId
    ,c1.CustomerName
    ,c1.PurchasePrice
    ,c1.Quantity
    ,c1.Loaddate
FROM dbo.CustomerTransaction1 c1
WHERE NOT EXISTS (
        SELECT *
        FROM dbo.CustomerTransaction2 c2
        WHERE c2.CustomerTransactionId = c1.CustomerTransactionId
            AND c2.CustomerName = c1.CustomerName
            AND c2.PurchasePrice = c1.PurchasePrice
            AND c2.Quantity = c1.Quantity
        );

--verify differences
select * from dbo.CustomerTransactionDiff

| CustomerTransactionId | CustomerName | PurchasePrice | Quantity | Loaddate                |
|-----------------------|--------------|---------------|----------|-------------------------|
| 1                     | Fred         | 100.00        | 5        | 2018-10-25 00:00:00.000 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy