Post reply

Combine or join 3 tables, 1 for each databases

  • August 17, 2022 at 3:11 pm

    #4077452

    Hello guys, I hope you're all great and healthy

    I would love to learn (and understand) how can I join those 3 tables from 3 different db.

    They all have the same table and column names.

    For all 3 tables I just want 2 columns: Code and Description AND a 3rd column which is a CONCAT of those 2 first columns plus a text like "ABC".

    When all 3 tables are joined I need to see only distinct values from Code because there will be duplicates.

    Thank you all in advance. I am very exciting to learn this

    Best regards

    Pedro

  • August 17, 2022 at 3:16 pm

    #4077454 

    SELECT code, description, code + ' ' + description AS RandomColumn
    FROM MyDB1.dbo.Table1
    UNION
    SELECT code, description, code + ' ' + description AS RandomColumn
    FROM MyDB2.dbo.Table3
    UNION
    SELECT code, description, code + ' ' + description AS RandomColumn
    FROM MyDB3.dbo.Table5;

    ?

    • This reply was modified 1 year, 8 months ago by  pietlinden.

  • August 17, 2022 at 3:31 pm

    #4077466

    It's amazing the simplicity of this solution (I bet it took you 2 seconds to think and another 2 to write it).

    And believe me, I've search on the web for almost 2 hours for this solution and never find it.

    And with this I've learn how to use UNION (instead of join) and also combining tables from other db.

    Thank you so much @pietlinden

    Great help

    Best regards

    Pedro

    P.S. There's is duplicates on my column code but if it is too difficult I will delete them in PBI

    • This reply was modified 1 year, 8 months ago by  pedroccamara.

  • August 17, 2022 at 5:34 pm

    #4077582

    The next step of Piet's recommendation can be found in the documentation for SQL Server.  Look for "Partitioned VIEWS".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2022 at 8:07 am

    #4077810

    Got it

    Thanks a lot

Viewing 5 posts - 1 through 4 (of 4 total)

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