SQL Query to Extract and Merge data

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 9:18 am

  #382497

  Hell 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 attached)
  Sample Delta Procesing_C360_Loy
  LCMS_MRD_Delta_LoyaltyAccount_2
  Merged_RAW__LoyaltyAccount
  ActiveRC_Prep_LotaltyAccount As I mentioned, I am comparing columns CDC_TYPE, PK_LOYALTYACCOUNT, and COUNTRY_ID in table Sample Delta Procesing_C360_Loy with table 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.

  I hope there is enough detail for someone to help me.

  I just want to make it clear that the output should look just like the sample table  Merged_RAW__LoyaltyAccount, and  ActiveRC_Prep_LotaltyAccount

  I have been accused of not providing enough detail, so I hope there is enough detail here for someone to help me. But if you need more detail, please don't hesitate to get in touch.

  I really really need your help with this.

  Carlton

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  December 20, 2018 at 11:21 am

  #2016979

  Most people are hesitant to open Excel files posted by random strangers on a public site.  The first link in my signature covers the proper way to post data.  Please follow it.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 1:20 pm

  #2016986

  drew.allen - Thursday, December 20, 2018 11:21 AM

  Most people are hesitant to open Excel files posted by random strangers on a public site.  The first link in my signature covers the proper way to post data.  Please follow it.

  Drew

  How about if I post it as a csv or txt file?

• Mike01

  SSChampion

  Points: 11395

  More actions

  December 20, 2018 at 1:51 pm

  #2016990

  how about posting ddl, data and expected result

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 2:19 pm

  #2016994

  OK, as I've not had much luck asking the question the way I have phrased it above I'm going to ask the same question but keeping it simple.

  So, I would like to know how to compare two database table records and return the results into separate table.
  For example, I have a total of 6 columns in each table A B C D E F. However, I'm only comparing on the records in columns A, B, & C
  If there is a match in in each table of columns A B C I would like the results to be outputted to new table (.csv file etc) for the row that matched

  Let me try and illustrate:

  In Table A you can see 6 columns, A B C D E F. The table has the following rows:
  

  Table B also has 6 columns A B C D E F and the following rows:
  
  As you can see the only match on A B C is xx PP dd
  I would like a sql query to identify that match and output the results in a new table with both rows that matched ie. in one row xx PP dd TYH ADF AD and another row xx PP dd tg ads afa
  I sincerely hope this simplifies the question and I get more responses.
  Thanks

• pietlinden

  SSC Guru

  Points: 63500

  More actions

  December 20, 2018 at 3:07 pm

  #2016996

  Do not pass GO. Do not collect $200.
  Go directly here and read: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  You'll get MUCH better answers if you read the article and follow the instructions when you post. The reason you're not getting any answers is that you don't provide any setup scripts or clear intended output. So most people just ignore your posting.

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 3:26 pm

  #2017000

  carlton 84646 - Thursday, December 20, 2018 2:19 PM

  OK, as I've not had much luck asking the question the way I have phrased it above I'm going to ask the same question but keeping it simple.

  So, I would like to know how to compare two database table records and return the results into separate table.
  For example, I have a total of 6 columns in each table A B C D E F. However, I'm only comparing on the records in columns A, B, & C
  If there is a match in in each table of columns A B C I would like the results to be outputted to new table (.csv file etc) for the row that matched

  Let me try and illustrate:

  In Table A you can see 6 columns, A B C D E F. The table has the following rows:
  

  Table B also has 6 columns A B C D E F and the following rows:
  
  As you can see the only match on A B C is xx PP dd
  I would like a sql query to identify that match and output the results in a new table with both rows that matched ie. in one row xx PP dd TYH ADF AD and another row xx PP dd tg ads afa
  I sincerely hope this simplifies the question and I get more responses.
  Thanks

  Are you being serious?
  OK, the intended output should look like the following:
  
  Do you notice how columns A, B & C have the same records.
  Can someone please help me

• Mike01

  SSChampion

  Points: 11395

  More actions

  December 20, 2018 at 3:37 pm

  #2017001

  I'm pretty sure I already responded to this in another forum, but here's what I guessed at based on what you've provided (no DDL, data, etc...)

  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

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 3:47 pm

  #2017002

  Mike01 - Thursday, December 20, 2018 3:37 PM

  I'm pretty sure I already responded to this in another forum, but here's what I guessed at based on what you've provided (no DDL, data, etc...)

  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

  Wow, can you let me know what forum you responded to

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 3:50 pm

  #2017003

  carlton 84646 - Thursday, December 20, 2018 3:47 PM

  Mike01 - Thursday, December 20, 2018 3:37 PM

  I'm pretty sure I already responded to this in another forum, but here's what I guessed at based on what you've provided (no DDL, data, etc...)

  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

  Wow, can you let me know what forum you responded to

  This is pretty cool Mike. I am curious to know what forum you responded to?

• carlton 84646

  Ten Centuries

  Points: 1026

  More actions

  December 20, 2018 at 4:40 pm

  #2017005

  Mike, 

  Just so you know it didn't work.
  Below is the output

• Mike01

  SSChampion

  Points: 11395

  More actions

  December 20, 2018 at 4:48 pm

  #2017006

  https://forums.sqlteam.com/t/sql-query-to-merge-extract-data/14668

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Mike01

  SSChampion

  Points: 11395

  More actions

  December 21, 2018 at 7:23 am

  #2017066

  That's what I get for guessing 😉  You see the DDL and sample data that I provided.  That is what we need along with what you are expecting as a result.  The fact that you posted "Just so you know it didn't work" means nothing.  Why didn't it work?  What were you expecting?

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/