How to get results from sql-jobs without running it every time

Question

I am new to SSC. My scenario is that I have created tables A, B, and C which are related to one another.

Whenever I need data from these three tables I always need to join them to get results. It's a bit time consuming to do this all the time.

Because of this I created a table 'R' and a procedure to update its contents. In this procedure I am joining all the tables (A, B, and C) and storing the result in table R.

To get the results into this table I create a SqlJob which runs once daily. However, there is a problem. Sometimes I want the results from A, B, and C tables where records were inserted recently (before R has been updated).

Is there any solution to get the result from the R table every time without running the SqlJob to update it constantly?

Additional Information

My desired solution is that any time I need data, table R is queried, not the joined tables A, B, and C. Your solution must take this into account.

Thank you.

Based on other feedback, this question may be better suited for StackExchange's other site [Programming Puzzles and Code Golf](http://codegolf.stackexchange.com). — STLDev, Oct 14 '16 at 00:35

STLDev · Answer 1 · 2016-10-13T07:25:51.183

1

Instead of running a procedure to constantly update table 'R', create a database view. This view would join A, B, and C together.

Then, any time you need to query A, B, and C, instead of risking getting stale data by querying table R, you would query the view.

I don't know your database schema, so I don't know what fields to join tables A, B, and C on, but it might look something like this:

CREATE VIEW V1
AS
SELECT * FROM A INNER JOIN B ON A.X = B.X INNER JOIN C ON B.Y = C.Y;

To query the view, you would use a SELECT statement just as you would with a table:

SELECT * FROM V1;

edited Oct 13 '16 at 07:25

answered Oct 13 '16 at 07:19

STLDev

5,950
25
36

thank you for your reply. but i want to use Procedure in my scenario. – RaghuValipe Oct 13 '16 at 14:58

score 0 · Answer 2 · answered Oct 13 '16 at 07:19

0

add a timex (timestamp) column in your R Table. so in any time you can get your latest result set.

answered Oct 13 '16 at 07:19

Hamid Jolany

800
7
11

if any column value is updated in any of the tables(a,b,c) , i need to run sqljob again to get the result From table-R. – RaghuValipe Oct 13 '16 at 14:57
write three triggeres for each table to start the job – Hamid Jolany Oct 14 '16 at 19:35

score 0 · Answer 3 · answered Oct 14 '16 at 00:33

Based on feedback from the OP that the table 'R' must always be the table queried (is this homework?), then I suppose the only solution would be to place an update trigger on each of the tables 'A', 'B', and 'C' so that when any of these tables are updated their updated contents are automatically placed in table 'R'.

Though inefficient, at least this is better than running a stored procedure on some time basis, for example every 5 minutes.

CREATE PROCEDURE [usp_SyncR]
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [R]
        SELECT * 
               GETUTCDATE() as [UpdatedOn]
            FROM A INNER JOIN B ON A.X = B.X INNER JOIN C ON B.Y = C.Y
END

CREATE TRIGGER [trg_A_Sync_R]
    ON [A]
AFTER Update
AS 
BEGIN
    EXEC [usp_SyncR];
END

CREATE TRIGGER [trg_B_Sync_R]
    ON [B]
AFTER Update
AS 
BEGIN
    EXEC [usp_SyncR];
END

CREATE TRIGGER [trg_C_Sync_R]
    ON [C]
AFTER Update
AS 
BEGIN
    EXEC [usp_SyncR];
END

How to get results from sql-jobs without running it every time

3 Answers3