none
Queries from multiple databases on the same server

    Question

  • I have a situation where I need to do some reporting for simular asset records but the data is listed on different database from the same server.

    I was curious as to the way to write the queries that I will need.

     

    Lets say I have 3 databases and I am looking for a asset of some type and the ID is a serial number.

    The serial will be on the main table from each database. Is there a way I can set a relation from a view between these records?

     

    I.E.:

    1: Database A -DSNA_tblMaiin - Serialnumber 

    2: Database B -DSNB_tblMaiin - Serialnumber 

    3: Database C -DSNC_tblMaiin - Serialnumber

     

    I believe that this is a dot notation question.

     

    Thanks in advance,

     

    Gene 

    Wednesday, February 13, 2008 2:15 PM

Answers

  • Absolutely.  You just need to make sure to reference each piece with the 3 or 4 part name.

     

    Database.Schema.Table when referencing tables

    and

    Database.Schema.Table.Column when referencing columns

     

    You can write joins between databases this way and deffinately pull data from more than one database.

     

    Code Snippet

    USE [DatabaseA]

    GO

     

    SELECT * FROM DatabaseA.dbo.DSNA_tblMaiin

    INNER JOIN DatabaseB.dbo.DSNB_tblMaiin ON DatabaseA.dbo.DSNA_tblMaiin.Serialnumber = DatabaseB.dbo.DSNB_tblMaiin.Serialnumber

    INNER JOIN DatabaseB.dbo.DSNC_tblMaiin ON DatabaseA.dbo.DSNA_tblMaiin.Serialnumber = DatabaseC.dbo.DSNC_tblMaiin.Serialnumber

     

     

    Wednesday, February 13, 2008 2:30 PM
  • If the serial is on the main table from EACH database, then you can just join the tables together using the fully qualified name:

    CREATE VIEW vWhatever

    AS

    SELECT (whatever you want to select)

    FROM [Database A].[schema - usually dbo].DSNA_tblMaiin AS a

    JOIN [Database B].[dbo].DSNB_tblMaiin AS b ON a.Serialnumber = b.SerialNumber

    JOIN [Database C].[dbo].DSNC_tblMaiin AS c ON a.SerialNumber = c.SerialNumber

    WHERE (whatever you want to filter by)

    GO

     

    If the assets were in one of the tables, but not all three (and you didn't know which one), you could use a UNION within the view to accomplish it - it will essentially bring all three tables together, appending the fields that you specify (of the same datatype) until you have one large "virtual table"  ex:

     

    CREATE VIEW vWhatever

    AS

    SELECT col1, col2, SerialNumber, etc.

    FROM [Database A].[dbo].DSNA_tblMaiin

    UNION ALL

    SELECT col1, col2, SerialNumber, etc.

    FROM [Database B].[dbo].DSNB_tblMaiin

    UNION ALL

    SELECT col1, col2, SerialNumber, etc.

    FROM [Database C].[dbo].DSNC_tblMaiin

    GO

     

    Dot notation is exactly what you're looking at.  And if you were extending the query to another server, it would just be [ServerName].[DatabaseName].[ObjectSchema].[ObjectName] instead of [DatabaseName].[ObjectSchema].[ObjectName]

     

    Let me know if this helps!

     

    Wednesday, February 13, 2008 2:32 PM
    Moderator

All replies

  • Absolutely.  You just need to make sure to reference each piece with the 3 or 4 part name.

     

    Database.Schema.Table when referencing tables

    and

    Database.Schema.Table.Column when referencing columns

     

    You can write joins between databases this way and deffinately pull data from more than one database.

     

    Code Snippet

    USE [DatabaseA]

    GO

     

    SELECT * FROM DatabaseA.dbo.DSNA_tblMaiin

    INNER JOIN DatabaseB.dbo.DSNB_tblMaiin ON DatabaseA.dbo.DSNA_tblMaiin.Serialnumber = DatabaseB.dbo.DSNB_tblMaiin.Serialnumber

    INNER JOIN DatabaseB.dbo.DSNC_tblMaiin ON DatabaseA.dbo.DSNA_tblMaiin.Serialnumber = DatabaseC.dbo.DSNC_tblMaiin.Serialnumber

     

     

    Wednesday, February 13, 2008 2:30 PM
  • If the serial is on the main table from EACH database, then you can just join the tables together using the fully qualified name:

    CREATE VIEW vWhatever

    AS

    SELECT (whatever you want to select)

    FROM [Database A].[schema - usually dbo].DSNA_tblMaiin AS a

    JOIN [Database B].[dbo].DSNB_tblMaiin AS b ON a.Serialnumber = b.SerialNumber

    JOIN [Database C].[dbo].DSNC_tblMaiin AS c ON a.SerialNumber = c.SerialNumber

    WHERE (whatever you want to filter by)

    GO

     

    If the assets were in one of the tables, but not all three (and you didn't know which one), you could use a UNION within the view to accomplish it - it will essentially bring all three tables together, appending the fields that you specify (of the same datatype) until you have one large "virtual table"  ex:

     

    CREATE VIEW vWhatever

    AS

    SELECT col1, col2, SerialNumber, etc.

    FROM [Database A].[dbo].DSNA_tblMaiin

    UNION ALL

    SELECT col1, col2, SerialNumber, etc.

    FROM [Database B].[dbo].DSNB_tblMaiin

    UNION ALL

    SELECT col1, col2, SerialNumber, etc.

    FROM [Database C].[dbo].DSNC_tblMaiin

    GO

     

    Dot notation is exactly what you're looking at.  And if you were extending the query to another server, it would just be [ServerName].[DatabaseName].[ObjectSchema].[ObjectName] instead of [DatabaseName].[ObjectSchema].[ObjectName]

     

    Let me know if this helps!

     

    Wednesday, February 13, 2008 2:32 PM
    Moderator
  • This would work as expected. However, if an asset can appear in more than one database then the UNION ALL would return duplicates. This could result in a cartesaen join should you then join the view to another table on the serialNumber.

     

    If you are only looking for a single asset at a time then you can you SELECT TOP 1 FROM the view. However, if you are selecting based on a range or groups of assets then you should consider using UNION rather than UNION ALL. It is important that you understand the difference. UNION has the impact of making the result set of the UNION distinct. This clearly comes with an overhead and, therefore, you should determine how you want to use the view before proceeding.

     

    Wednesday, February 13, 2008 2:46 PM
  •  

    hi all,

     

    i am attempting the same here, to query different tables from two databases. however, the database.schema.table doesnt seem to work 

    here is what i currently have:

    DB 1-> table1;

    DB2-> table2,table3

    my query is:

    SELECT table2.ID, table2.Date, table3.Item,table3.Operation, table3.Op, table3.ID AS MonID,

    table2.Transaction, table2.Name, table3.IncludeName, table3.IncludeOp,DB1.dbo.table1.Address

    FROM table2 INNER JOIN

    table3 ON table3.Item = table3.ID INNER JOIN

    DB1.dbo.table1 ON table2.ID = DB1.dbo.table1.ID

    WHERE Trans_Date >= '12:07:54 07/15/2008'

     

    i am getting the error:Invalid object name table;i know for sure that table exists in DB1 can anyone please help me out?

    Thursday, July 31, 2008 8:41 AM
  • Are you sure they were all created under the dbo schema?  It's possible that you have multiple schemas at hand here.

     

    Friday, August 01, 2008 12:49 AM
    Moderator
  •  

    hi,

     

    i checked, it is created under the dbo schema. if i cant access the table directly using one query, maybe i can use two separate queries. since i need to access the db from my C# app, maybe i can store the results of the 1st query in a variable, then use it in the second query. i will try it out and get back to you. mean while, any suggestions are welcome

    Friday, August 01, 2008 3:05 AM