locked
Find which Table Holds Data RRS feed

  • Question

  • One of our servers is on it's way out, and until we can fully get the "new" one online we are using it as a roll-over for when the old one fails.  So, when the old server begins to fail, the data will be written to the new server which makes querying a nightmare (at least for me).  What I need to do is write a procedure that can iterate a range of dates and for each individual date check each table to determine where to pull the data from.  I put together some sample data/table structure which shows the relevant information.  Also, I have put together a query, but it the execution time is to slow to actually use.  Can someone help me with this?

    Edit - I realize that I only showed print statements.  What I want to do is produce ONE query that will show all entries spanning all days sequentially.

    Create Table #FirstOne
    (
    	FID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    	,logid varchar(30)
    	,logdate datetime
    )
    
    Create Table #SecondOne
    (
    	skID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    	,logid varchar(30)
    	,logdate datetime
    )
    
    Insert Into #FirstOne Values
    ('ergh', '01/01/2012 08:00:00.000')
    ,('ergh', '01/01/2012 08:00:23.000')
    ,('ergh', '01/01/2012 08:00:46.000')
    ,('ergh', '01/02/2012 08:00:23.000')
    ,('ergh', '01/02/2012 08:00:46.000')
    ,('ergh', '01/04/2012 08:00:23.000')
    ,('ergh', '01/05/2012 08:00:46.000')
    ,('ergh', '01/08/2012 08:00:46.000')
    
    Insert Into #SecondOne Values
    ('ergh', '01/03/2012 08:00:00.000')
    ,('ergh', '01/06/2012 08:00:23.000')
    ,('ergh', '01/06/2012 08:30:46.000')
    ,('ergh', '01/07/2012 08:00:23.000')
    ,('ergh', '01/07/2012 08:52:46.000')
    
    DECLARE @Flag INT;
    Declare @logID varchar(50), @d1 datetime,@d2 datetime,@firstdate date 
    Set @logID = 'ergh'
    Set @d1 = '01/01/2012'
    Set @d2 = '01/15/2012'
    
    Declare c1 Cursor For
    
    Select d
    FROM annualcal
    WHERE d BETWEEN @d1 AND @d2
    ORDER BY d
    
    Open c1 
    
    Fetch Next From c1 Into @firstdate
    
    While @@FETCH_STATUS = 0
    Begin
    
    WITH CTE1
         AS (SELECT 1 AS Priority, logid, logdate
             FROM    #FirstOne
             UNION ALL
             SELECT 2 AS Priority, logid, logdate
             FROM   #SecondOne),
         CTE2
         AS (SELECT *
             FROM   CTE1
             WHERE  logid = @logid
                    AND logdate >= @firstdate
                    AND logdate < @firstdate)
    SELECT @Flag =
            CASE WHEN EXISTS (SELECT * FROM CTE2 WHERE Priority = 1) THEN 1
                 WHEN EXISTS (SELECT * FROM CTE2 WHERE Priority = 2) THEN 2
            END
            
     Print @firstdate  
     Print @flag
     
    Fetch Next From c1 Into @firstdate
    
    End
    
    Close c1
    Deallocate c1



    Tuesday, December 29, 2015 9:46 PM

Answers

  • Hi BeginerOfCode,

    As mentioned by Patrick Hurst, linked server might be taken into consideration first. As for your sample script, please check if the following code is what you want.

    Create Table #FirstOne
    (
    	FID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    	,logid varchar(30)
    	,logdate datetime
    )
    
    Create Table #SecondOne
    (
    	skID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    	,logid varchar(30)
    	,logdate datetime
    )
    
    Insert Into #FirstOne Values
    ('ergh', '01/01/2012 08:00:00.000')
    ,('ergh', '01/01/2012 08:00:23.000')
    ,('ergh', '01/01/2012 08:00:46.000')
    ,('ergh', '01/02/2012 08:00:23.000')
    ,('ergh', '01/02/2012 08:00:46.000')
    ,('ergh', '01/04/2012 08:00:23.000')
    ,('ergh', '01/05/2012 08:00:46.000')
    ,('ergh', '01/08/2012 08:00:46.000')
    
    Insert Into #SecondOne Values
    ('ergh', '01/03/2012 08:00:00.000')
    ,('ergh', '01/06/2012 08:00:23.000')
    ,('ergh', '01/06/2012 08:30:46.000')
    ,('ergh', '01/07/2012 08:00:23.000')
    ,('ergh', '01/07/2012 08:52:46.000')
    
    Declare @logID varchar(50), @d1 datetime,@d2 datetime
    Set @logID = 'ergh'
    Set @d1 = '01/01/2012'
    Set @d2 = '01/15/2012'
    
    Declare @annualcal table
    (
    	d date
    )
    
    Insert Into @annualcal Values
    ('01/01/2012')
    ,('01/02/2012')
    ,('01/03/2012')
    ,('01/04/2012')
    ,('01/05/2012')
    ,('01/06/2012')
    ,('01/07/2012')
    ,('01/08/2012')
    ,('01/09/2012')
    
    Select d,
    		CASE
    			WHEN EXISTS (SELECT 1 FROM #FirstOne
    							WHERE  logid = @logid
    							AND logdate >= d
    							AND logdate < dateadd(day,1,d)) THEN 1
    			WHEN EXISTS (SELECT 1 FROM #SecondOne
    							WHERE  logid = @logid
    							AND logdate >= d
    							AND logdate < dateadd(day,1,d)) THEN 2
            END AS FLAG
    FROM @annualcal
    WHERE d BETWEEN @d1 AND @d2
    ORDER BY d
    
    drop table #FirstOne;
    drop table #SecondOne;


    Sam Zha
    TechNet Community Support

    • Proposed as answer by Ahsan KabirMVP Wednesday, December 30, 2015 12:52 PM
    • Marked as answer by BeginerOfCode Wednesday, December 30, 2015 1:01 PM
    Wednesday, December 30, 2015 7:42 AM

All replies

  • Create a linked server to the old server from the new one

    Create a view which unions the remote (old) and local (new) tables together.

    Query the view instead.

    You can also add a column to the end of each side of the union like 'OldServer' AS dbSource


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.


    Tuesday, December 29, 2015 10:11 PM
  • Create a linked server to the old server from the new one

    Create a view which unions the remote (old) and local (new) tables together.

    Query the view instead.

    You can also add a column to the end of each side of the union like 'OldServer' AS dbSource


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left) 

    Would it be more efficient to create and drop this view each time this data needs to be collected (3 times a day at most) than to outright query the individual tables?

    Wednesday, December 30, 2015 2:01 AM
  • Hi BeginerOfCode,

    As mentioned by Patrick Hurst, linked server might be taken into consideration first. As for your sample script, please check if the following code is what you want.

    Create Table #FirstOne
    (
    	FID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    	,logid varchar(30)
    	,logdate datetime
    )
    
    Create Table #SecondOne
    (
    	skID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    	,logid varchar(30)
    	,logdate datetime
    )
    
    Insert Into #FirstOne Values
    ('ergh', '01/01/2012 08:00:00.000')
    ,('ergh', '01/01/2012 08:00:23.000')
    ,('ergh', '01/01/2012 08:00:46.000')
    ,('ergh', '01/02/2012 08:00:23.000')
    ,('ergh', '01/02/2012 08:00:46.000')
    ,('ergh', '01/04/2012 08:00:23.000')
    ,('ergh', '01/05/2012 08:00:46.000')
    ,('ergh', '01/08/2012 08:00:46.000')
    
    Insert Into #SecondOne Values
    ('ergh', '01/03/2012 08:00:00.000')
    ,('ergh', '01/06/2012 08:00:23.000')
    ,('ergh', '01/06/2012 08:30:46.000')
    ,('ergh', '01/07/2012 08:00:23.000')
    ,('ergh', '01/07/2012 08:52:46.000')
    
    Declare @logID varchar(50), @d1 datetime,@d2 datetime
    Set @logID = 'ergh'
    Set @d1 = '01/01/2012'
    Set @d2 = '01/15/2012'
    
    Declare @annualcal table
    (
    	d date
    )
    
    Insert Into @annualcal Values
    ('01/01/2012')
    ,('01/02/2012')
    ,('01/03/2012')
    ,('01/04/2012')
    ,('01/05/2012')
    ,('01/06/2012')
    ,('01/07/2012')
    ,('01/08/2012')
    ,('01/09/2012')
    
    Select d,
    		CASE
    			WHEN EXISTS (SELECT 1 FROM #FirstOne
    							WHERE  logid = @logid
    							AND logdate >= d
    							AND logdate < dateadd(day,1,d)) THEN 1
    			WHEN EXISTS (SELECT 1 FROM #SecondOne
    							WHERE  logid = @logid
    							AND logdate >= d
    							AND logdate < dateadd(day,1,d)) THEN 2
            END AS FLAG
    FROM @annualcal
    WHERE d BETWEEN @d1 AND @d2
    ORDER BY d
    
    drop table #FirstOne;
    drop table #SecondOne;


    Sam Zha
    TechNet Community Support

    • Proposed as answer by Ahsan KabirMVP Wednesday, December 30, 2015 12:52 PM
    • Marked as answer by BeginerOfCode Wednesday, December 30, 2015 1:01 PM
    Wednesday, December 30, 2015 7:42 AM
  • As long as the underlying DDL has no changed, there is no need to drop and recreate the view.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, December 30, 2015 2:20 PM