Merge Tables with SQL Query Request

Question

Merge Tables with SQL Query Request

carlton 84646

SSC Eights!

Points: 902
More actions
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

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

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

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 1

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

carlton 84646 SSC Eights! Points: 902 More actions · Answer 2

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.

carlton 84646 SSC Eights! Points: 902 More actions · Answer 3

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?

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 4

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

carlton 84646 SSC Eights! Points: 902 More actions · Answer 5

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

Roland Alexander STL SSCrazy Points: 2915 More actions · Answer 6

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

jcelko212 32090 SSCrazy Eights Points: 9220 More actions · Answer 7

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.

sgmunson SSC Guru Points: 110588 More actions · Answer 8

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)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)