Data from two databases

  • Hi,

    I'm new to SQL.

    I have data in two tables in two differnet databases.

    The databases are for the same service and on the same server.

    I need to merge data from a table in each database but i'm having no luck, i have tried UNION but it doesn't work for me.

    Any help would be great.

    Many thanks

  • What exactly do yo mean by "doesn't work"?

    Please remember we can't look over your shoulder nor can we access your system.

    If you get an error message, please post it together with the script you use.

    If you don't get the expected result, please set up a test scenario with sample data to reproduce the issue and post the data together with your expected result. For a reference how to post sample data please have a look at the first link in my signature.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi,

    Thanks for getting back to me so quickly.

    The error is - Msg 156, Level 15, State 1, Line 17

    Incorrect syntax near the keyword 'Use'.

    The script is:

    Use DataBase1

    SELECT dbo.DV_DEVICE_T.DEVICE_NAME, dbo.PWR_L_PowerStrips.DEVICE_NAME AS STRIP_NAME

    FROM dbo.PWR_L_PowerStrips

    INNER JOIN

    dbo.PWR_OF_Receptacles_and_Connections ON

    dbo.PWR_L_PowerStrips.VOB_ID = dbo.PWR_OF_Receptacles_and_Connections.HOLD_DEVICE_VOB_ID

    RIGHT OUTER JOIN

    dbo.DV_DEVICE_T ON dbo.PWR_OF_Receptacles_and_Connections.DEVICE_VOB_ID = dbo.DV_DEVICE_T.VOB_ID

    WHERE (dbo.DV_DEVICE_T.ASSET_CLASS <> 'CABINET')

    UNION

    Use DataBase2

    SELECT AWHdrForm.Ticket, AWHdrForm.AppType, EMAC_I_Device.SYSTEM_NAME AS DEVICE_NAME, EMAC_Install.BUILDING,

    EMAC_I_Device.SPACE_NAME AS SPACE, AWHistory.ActionEnd

    FROM AWMultiForm INNER JOIN

    AWHistory ON AWMultiForm.MultiFormID = AWHistory.FormID INNER JOIN

    EMAC_Install ON AWMultiForm.MultiFormID = EMAC_Install.FormID INNER JOIN

    EMAC_I_Device ON AWMultiForm.FormID = EMAC_I_Device.FormID INNER JOIN

    AWHdrForm ON AWMultiForm.MultiFormID = AWHdrForm.FormID

    WHERE (AWHistory.ActionName = 'Installation') AND (AWHistory.ActionEnd IS NOT NULL)

    ORDER BY EMAC_Install.BUILDING, SPACE, AWHdrForm.Ticket

  • "Use" should not be used just after the "UNION". Thats the issue here.

    You have to provide 4 part table to access the same.

  • Hi,

    I'm sorry, what is a four part table?

  • A table is qualified by INSTANCE.DATABASE.SCHEMA.TABLE.

    Within a single database it's sufficient to use SCHEMA.TABLE (like you did when using dbo.PWR_L_PowerStrips). Some folks even omit the schema...

    When using queries across databases on the same instance, you need to use the three-part name to qualify a table from a different db. (in your case: instead of dbo.AWMultiForm it needs to be DataBase2.dbo.AWMultiForm). And if the database is on a different instance, this one needs to be qualified, too, leading to the four-part-name sqlzealot mentioned.

    Your query should work if you use the three-part name to identify the tables on the other db:

    SELECT dbo.DV_DEVICE_T.DEVICE_NAME, dbo.PWR_L_PowerStrips.DEVICE_NAME AS STRIP_NAME

    FROM dbo.PWR_L_PowerStrips

    INNER JOIN

    dbo.PWR_OF_Receptacles_and_Connections ON

    dbo.PWR_L_PowerStrips.VOB_ID = dbo.PWR_OF_Receptacles_and_Connections.HOLD_DEVICE_VOB_ID

    RIGHT OUTER JOIN

    dbo.DV_DEVICE_T ON dbo.PWR_OF_Receptacles_and_Connections.DEVICE_VOB_ID = dbo.DV_DEVICE_T.VOB_ID

    WHERE (dbo.DV_DEVICE_T.ASSET_CLASS <> 'CABINET')

    UNION

    SELECT AWHdrForm.Ticket, AWHdrForm.AppType, EMAC_I_Device.SYSTEM_NAME AS DEVICE_NAME, EMAC_Install.BUILDING,

    EMAC_I_Device.SPACE_NAME AS SPACE, AWHistory.ActionEnd

    FROM DataBase2.dbo.AWMultiForm INNER JOIN

    DataBase2.dbo.AWHistory ON AWMultiForm.MultiFormID = AWHistory.FormID INNER JOIN

    DataBase2.dbo.EMAC_Install ON AWMultiForm.MultiFormID = EMAC_Install.FormID INNER JOIN

    DataBase2.dbo.EMAC_I_Device ON AWMultiForm.FormID = EMAC_I_Device.FormID INNER JOIN

    DataBase2.dbo.AWHdrForm ON AWMultiForm.MultiFormID = AWHdrForm.FormID

    WHERE (AWHistory.ActionName = 'Installation') AND (AWHistory.ActionEnd IS NOT NULL)

    ORDER BY EMAC_Install.BUILDING, SPACE, AWHdrForm.Ticket



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • One more issue:

    If you know for sure there won't be any duplicate values you could use UNION ALL instead of UNION. Otherwise SQL Server would have to perform an aggregation to eliminate potential duplicates.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi Lutz,

    This is great, thank you very much for getting back to me so quickly.

    Thanks again

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

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