Post reply

Merge Tables with SQL Query Request

  • December 18, 2018 at 5:35 am

    #378908

    Hello Community,

    Can someone please take a look at the following tables and let me know how to compile a sql script to merge the tables.

    For example I would like to create table showing the delta between 'LCMS_MRD_Delta_Product_2018-12-18 10-21-55' and 'LCMS_MRD_Delta_Product_2018-12-18 10-22-26' and name the delta table something like merged1. 

    I would then like to use the sql script to create and delta table between merged1 and 'LCMS_MRD_Delta_Product_2018-12-18 10-22-51' and call that table merged2. 

    I would then like to use the sql script to create and delta table between merged2 and 'LCMS_MRD_Delta_Product_2018-12-18 10-23-18' and call that table merged3

    I would then like to use the sql script to create a delta table between merged3 and 'LCMS_MRD_Delta_Product_2018-12-18 10-23-44' and call that table 'finalmerge'

    The tables appear as follows:

    The table names are as follows:

    Any help will be greatly appreciated

    Carlton

  • December 18, 2018 at 6:19 am

    #2016643

    If the structure of each table is identical (same columns, data types), and if by "merge" you mean "combine", then investigate UNION and UNION ALL. The former will combine all rows from one query with all rows from a second, while excluding duplicates. The latter includes duplicates.
    If you mean something else by "merge", you're going to need to provide more detail.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • December 18, 2018 at 6:32 am

    #2016645

    Roland Alexander STL - Tuesday, December 18, 2018 6:19 AM

    If the structure of each table is identical (same columns, data types), and if by "merge" you mean "combine", then investigate UNION and UNION ALL. The former will combine all rows from one query with all rows from a second, while excluding duplicates. The latter includes duplicates.
    If you mean something else by "merge", you're going to need to provide more detail.

    Hi SSCrazy,
    Thanks for reaching out.
    I'm sure you can see that I'm a novice. Therefore, I actually mean UNION. Unfortunately, I don't know what more information I need to give you.

  • December 18, 2018 at 6:34 am

    #2016646

    carlton 84646 - Tuesday, December 18, 2018 6:32 AM

    Roland Alexander STL - Tuesday, December 18, 2018 6:19 AM

    If the structure of each table is identical (same columns, data types), and if by "merge" you mean "combine", then investigate UNION and UNION ALL. The former will combine all rows from one query with all rows from a second, while excluding duplicates. The latter includes duplicates.
    If you mean something else by "merge", you're going to need to provide more detail.

    Hi SSCrazy,
    Thanks for reaching out.
    I'm sure you can see that I'm a novice. Therefore, I actually mean UNION. Unfortunately, I don't know what more information I need to give you.

    SSCrazy, i have just re-read the question and I definitely need merge whereby you say "The former will combine all rows from one query with all rows from a second, while excluding duplicates". This is what I need. Can you help me with that?

  • December 18, 2018 at 6:40 am

    #2016648

    Hi Carlton,
    "SSCrazy" isn't my screen name, it's my rating on SSC 🙂

    I just re-read your question myself, and I missed the word "delta" (haste makes waste!). A delta query would show the difference between two tables: for example, the rows in table A that are not in table B. UNION will combine the rows. For example, if table A has 10 rows, and table B 8 rows, delta will produce two rows, UNION 12. So: what exactly are we trying to do here?

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • December 18, 2018 at 7:38 am

    #2016661

    Roland Alexander STL - Tuesday, December 18, 2018 6:40 AM

    Hi Carlton,
    "SSCrazy" isn't my screen name, it's my rating on SSC 🙂

    I just re-read your question myself, and I missed the word "delta" (haste makes waste!). A delta query would show the difference between two tables: for example, the rows in table A that are not in table B. UNION will combine the rows. For example, if table A has 10 rows, and table B 8 rows, delta will produce two rows, UNION 12. So: what exactly are we trying to do here?

    Hi Roland,
    Thanks for sticking with me on this.
    I would like a UNION 12.

    Thanks

  • December 18, 2018 at 8:36 am

    #2016679

    carlton 84646 - Tuesday, December 18, 2018 7:38 AM

    Roland Alexander STL - Tuesday, December 18, 2018 6:40 AM

    Hi Carlton,
    "SSCrazy" isn't my screen name, it's my rating on SSC 🙂

    I just re-read your question myself, and I missed the word "delta" (haste makes waste!). A delta query would show the difference between two tables: for example, the rows in table A that are not in table B. UNION will combine the rows. For example, if table A has 10 rows, and table B 8 rows, delta will produce two rows, UNION 12. So: what exactly are we trying to do here?

    Hi Roland,
    Thanks for sticking with me on this.
    I would like a UNION 12.

    Thanks

    UNION is easy:
    SELECT * FROM LCMS_MRD_Delta_Product_2018-12-18 10-21-55
    UNION
    SELECT * FROM LCMS_MRD_Delta_Product_2018-12-18 10-22-26
    UNION
    SELECT * FROM LCMS_MRD_Delta_Product_2018-12-18 10-22-51
    UNION
    SELECT * FROM LCMS_MRD_Delta_Product_2018-12-18 10-23-18
    UNION
    SELECT * FROM LCMS_MRD_Delta_Product_2018-12-18 10-23-44;

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • December 19, 2018 at 9:21 am

    #2016837

    carlton 84646 - Tuesday, December 18, 2018 5:35 AM

    You might want to read the front of every SQL forum. Instead of posting pictures, we want you to post DDL, so we know what your data actually looks like. We have no idea about keys, constraints datatypes or anything from your picture. It's also a bitch to transcribe your data from a blowup of that spreadsheet you posted. I'd also like to know what a Delta table is; I never use that term.

    What you seem to be describing is a design error called "attribute splitting", which means that things it should be in one table have been put in two or more tables. The attribute you split on probably should have been a column in a base table from your narrative. Essentially, your attempts at a UNION-ed result seems to be an attempt to repair what you should of had in the first place. To make this a little clearer, the example I pull up my books is would you ever have a "Female_Personnel" and a "Male_Personnel" tables, or a single "Personnel" with a column called sex_code? The other advantage is that if you start hiring corporations (Manpower, TaskRabbit, etc). All you have to do is follow the ANSI/ISO six code standards in use 9= lawful person instead of creating a whole new table.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • December 19, 2018 at 11:25 am

    #2016863

    jcelko212 32090 - Wednesday, December 19, 2018 9:21 AM

    carlton 84646 - Tuesday, December 18, 2018 5:35 AM

    You might want to read the front of every SQL forum. Instead of posting pictures, we want you to post DDL, so we know what your data actually looks like. We have no idea about keys, constraints datatypes or anything from your picture. It's also a bitch to transcribe your data from a blowup of that spreadsheet you posted. I'd also like to know what a Delta table is; I never use that term.

    What you seem to be describing is a design error called "attribute splitting", which means that things it should be in one table have been put in two or more tables. The attribute you split on probably should have been a column in a base table from your narrative. Essentially, your attempts at a UNION-ed result seems to be an attempt to repair what you should of had in the first place. To make this a little clearer, the example I pull up my books is would you ever have a "Female_Personnel" and a "Male_Personnel" tables, or a single "Personnel" with a column called sex_code? The other advantage is that if you start hiring corporations (Manpower, TaskRabbit, etc). All you have to do is follow the ANSI/ISO six code standards in use 9= lawful person instead of creating a whole new table.

    As usual, Joe, you just bloviate over standards instead of asking the user the "why" question.  There are times when standards just don't apply, because sometimes you have to get stuff done, good, bad, or even ugly.   I'm not suggesting any of those things are a necessity, but one doesn't always have the luxury of time enough to do things that way, or a standard may not even need to apply, like when you do it wrong on purpose to prove why it's bad.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply