SQL Query to Merge / Extract Data

Transact-SQL
1

Hello Community,

Can someone please take a look at the tables in the attached file and help compile a sql query that will compare the columns CDC_TYPE , PK_LOYALTYACCOUNT , and COUNTRY_ID from tables Sample Delta Procesing_C360_Loy , and LCMS_MRD_Delta_LoyaltyAccount_2 and if there is a match add the original to the column called Is_Deleted in the table called Merged_RAW__LoyaltyAccount with the number 1 and the new CDC _TYPE in the row below.

Let me provide a sample with an explanation

There are four tables (even though all tables are shown one on spreadsheet for clarity, see images)

Sample Delta Procesing_C360_Loy

LCMS_MRD_Delta_LoyaltyAccount_2

Merged_RAW__LoyaltyAccount

ActiveRC_Prep_LotaltyAccount

Sample%20Delta%20Procesing_C360_Loy
Sample Delta Procesing_C360_Loy.png1569×252 19.3 KB

As I mentioned, I am comparing columns CDC_TYPE, PK_LOYALTYACCOUNT, and COUNTRY_ID in the following tables Sample Delta Procesing_C360_Loy with LCMS_MRD_Delta_LoyaltyAccount_2.

You can see that there is a match on CDC_TYPE, U , PK_LOYALTYACCOUNT, 300021 , and COUNTRY_ID, DE in table Sample Delta Procesing_C360_Loy (orginal table) and table LCMS_MRD_Delta_LoyaltyAccount_2.

After we have identified the above match I would like the match from the original file added to the table Merged_RAW__LoyaltyAccount and the included in the ‘ Is_Deleted ’ column with the number 1, and the new match in the immediate row below.

In table ActiveRC_Prep_LotaltyAccount I would just like the new match added to the row.

I hope I have provided enough detail for you to help me.

You're help will be very much appreciated.

Thanks

2

I need to add the other tables

LCMS_MRD_Delta_LoyaltyAccount_2
LCMS_MRD_Delta_LoyaltyAccount_2.png1584×272 19.7 KB

3

Sorry about the adding the tables like this. this site only allows newbies to add one image at a time.

Merged_RAW__LoyaltyAccount
Merged_RAW__LoyaltyAccount.png1584×255 22 KB

4

and the final table looks like this

ActiveRC_Prep_LotaltyAccount
ActiveRC_Prep_LotaltyAccount.png1579×271 19.3 KB

5

It would be so much easier if you added DDL and data along with expected results. Screen shots don't help with setup

6

here's my best guess at what you want

drop table if exists #Sample_Delta_Procesing_C360_Loy
go
drop table if exists #LCMS_MRD_Delta_LoyaltyAccount_2
go
drop table if exists #Merged_RAW__LoyaltyAccount
go
drop table if exists #ActiveRC_Prep_LotaltyAccount
go

create table #Sample_Delta_Procesing_C360_Loy
	(CDC_Type char(1),
	 PK_LOYALTYACCOUNT varchar(10),
	 COUNTRY_ID char(2),
	 ClassMark varchar(10))

create table #LCMS_MRD_Delta_LoyaltyAccount_2
	(CDC_Type char(1),
	 PK_LOYALTYACCOUNT varchar(10),
	 COUNTRY_ID char(2),
	 ClassMark varchar(10)	 )

create table #Merged_RAW__LoyaltyAccount
	(CDC_Type char(1),
	 PK_LOYALTYACCOUNT varchar(10),
	 COUNTRY_ID char(2),
	 ClassMark varchar(10),
	 IsDeleted bit)

create table #ActiveRC_Prep_LotaltyAccount
	(CDC_Type char(1),
	 PK_LOYALTYACCOUNT varchar(10),
	 COUNTRY_ID char(2),
	 ClassMark varchar(10))

insert into #Sample_Delta_Procesing_C360_Loy (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark) values 
('U','300021','DE','CRT'),
('N','51325','DE','Private'),
('D','50599','DE','Private'),
('U','300022','DE','CRT')

insert into #LCMS_MRD_Delta_LoyaltyAccount_2 (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark) values 
('U','300021','DE','Private'),
('N','513215','DE','Private'),
('D','300022','DE','CRT')

;with cte as (
select orig.CDC_Type as orig_CDC_Type, orig.PK_LOYALTYACCOUNT as orig_PK_LOYALTYACCOUNT, 
	   orig.COUNTRY_ID as orig_COUNTRY_ID, orig.ClassMark as orig_ClassMark,
	   loy2.CDC_Type as loy2_CDC_Type, loy2.PK_LOYALTYACCOUNT as loy2_PK_LOYALTYACCOUNT, 
	   loy2.COUNTRY_ID as loy2_COUNTRY_ID, loy2.ClassMark as loy2_ClassMark
  from #Sample_Delta_Procesing_C360_Loy orig
	join #LCMS_MRD_Delta_LoyaltyAccount_2 loy2
		on orig.CDC_Type = loy2.CDC_Type
	   and orig.PK_LOYALTYACCOUNT = loy2.PK_LOYALTYACCOUNT
	   and orig.COUNTRY_ID = loy2.COUNTRY_ID)

insert into #Merged_RAW__LoyaltyAccount (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark, IsDeleted) 
select orig_CDC_Type as CDC_Type, orig_PK_LOYALTYACCOUNT as LOYALTYACCOUNT, 
	   orig_COUNTRY_ID as COUNTRY_ID, orig_ClassMark as ClassMark, cast(0 as bit) as IsDeleted
 from cte
union all
select loy2_CDC_Type as CDC_Type, loy2_PK_LOYALTYACCOUNT as LOYALTYACCOUNT, 
	   loy2_COUNTRY_ID as COUNTRY_ID, loy2_ClassMark as ClassMark, cast(1 as bit) as IsDeleted
 from cte

 ;with cte as (
select orig.CDC_Type as orig_CDC_Type, orig.PK_LOYALTYACCOUNT as orig_PK_LOYALTYACCOUNT, 
	   orig.COUNTRY_ID as orig_COUNTRY_ID, orig.ClassMark as orig_ClassMark,
	   loy2.CDC_Type as loy2_CDC_Type, loy2.PK_LOYALTYACCOUNT as loy2_PK_LOYALTYACCOUNT, 
	   loy2.COUNTRY_ID as loy2_COUNTRY_ID, loy2.ClassMark as loy2_ClassMark
  from #Sample_Delta_Procesing_C360_Loy orig
	join #LCMS_MRD_Delta_LoyaltyAccount_2 loy2
		on orig.CDC_Type = loy2.CDC_Type
	   and orig.PK_LOYALTYACCOUNT = loy2.PK_LOYALTYACCOUNT
	   and orig.COUNTRY_ID = loy2.COUNTRY_ID)

insert into #ActiveRC_Prep_LotaltyAccount (CDC_Type, PK_LOYALTYACCOUNT, COUNTRY_ID, ClassMark) 
select loy2_CDC_Type as CDC_Type, loy2_PK_LOYALTYACCOUNT as LOYALTYACCOUNT, 
	   loy2_COUNTRY_ID as COUNTRY_ID, loy2_ClassMark as ClassMark
 from cte

select * from #Merged_RAW__LoyaltyAccount
select * from #ActiveRC_Prep_LotaltyAccount