August 26, 2011 at 2:51 am
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
August 26, 2011 at 3:05 am
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.
August 26, 2011 at 4:31 am
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
August 26, 2011 at 4:36 am
"Use" should not be used just after the "UNION". Thats the issue here.
You have to provide 4 part table to access the same.
August 26, 2011 at 4:42 am
Hi,
I'm sorry, what is a four part table?
August 26, 2011 at 4:53 am
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
August 26, 2011 at 4:55 am
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.
August 26, 2011 at 6:49 am
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