none
how to fetch the recordes from multiple tables across the databases in a same instance?

    Question

  • Hi All,

    Is it possible to fetch the recordes from multiple tables across the databases with between the dates?

    for example....i want to fetch the recordes from a and b databases in same sql instance.

    if it is possible please provide the sample query..

    Thanks in Advance,


    rup

    Wednesday, October 09, 2013 9:26 PM

Answers

  • Yes, you can use three part naming to access table cross the DBs

    PFB code

    declare @getdate datetime = convert(varchar(10),getdate(),121)
    select customerid from [DB1].dbo.table1 where crdate >= @getdate and crdate <= CONVERT(varchar(10),@getdate,121) + ' 23:59:59.000'
    union all
    select customerid from [DB2].dbo.table1 where crdate >= @getdate and crdate <= CONVERT(varchar(10),@getdate,121) + ' 23:59:59.000'

    Thanks

    Saravana Kumar C

    Thursday, October 10, 2013 3:46 AM

All replies

  • Use three part identifier names for tables.

    Like if you want to access dbo.customer table in database a,you use the following query

    select * from a.dbo.customer

    Like wise

    you can use tables from multiple databases in the same query.

    select * 
    from a.dbo.cusomer a_cus
    join b.dbo.customer b_cus on a.cus.id=b.cus_id



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer



    • Edited by Vinay Valeti Wednesday, October 09, 2013 9:38 PM
    Wednesday, October 09, 2013 9:35 PM
  • CREATE VIEW NEWVIEW
    AS
    SELECT *
    FROM [Database A].[dbo].DB_Main
    UNION ALL
    SELECT *
    FROM [Database B].[dbo].DBB_Main
    UNION ALL
    SELECT *
    FROM [Database C].[dbo].DBBB_Main
    GO
    
    If these tables can be joined you can do JOIN instead of UNION.
    Wednesday, October 09, 2013 9:44 PM
  • Yes, you can use three part naming to access table cross the DBs

    PFB code

    declare @getdate datetime = convert(varchar(10),getdate(),121)
    select customerid from [DB1].dbo.table1 where crdate >= @getdate and crdate <= CONVERT(varchar(10),@getdate,121) + ' 23:59:59.000'
    union all
    select customerid from [DB2].dbo.table1 where crdate >= @getdate and crdate <= CONVERT(varchar(10),@getdate,121) + ' 23:59:59.000'

    Thanks

    Saravana Kumar C

    Thursday, October 10, 2013 3:46 AM