Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using SQL Server 2008. I have two databases called Adb and Bdb. There is a common table called commonTable. How can I update Adb database every 15 minutes using the data from the same table in Bdb?

share|improve this question
4  
You need to explain what you mean. For example, do you want the contents of Bdb to completely overwrite Adb every time? Or are you more looking to merge (e.g. delete rows in A that have been deleted in B, update rows that have changed, insert rows that have been added)? If the latter, how do you identify a row in the tables? Can you provide schema and indicate the primary key? –  Aaron Bertrand Dec 11 '12 at 23:12
add comment

1 Answer

up vote 8 down vote accepted

Without more specifics:

CREATE PROCEDURE dbo.UpdateA
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE A 
    SET col1 = B.col1 /* , ... */
    FROM Adb.dbo.commonTable AS A
    INNER JOIN Bdb.dbo.commonTable AS B
    ON A.key_col = B.key_col;

  INSERT Adb.dbo.commonTable(key_col, col1 /* , ... */)
    SELECT key_col, col1 /* , ... */
    FROM Bdb.dbo.commonTable AS B
    WHERE NOT EXISTS (SELECT 1 FROM Adb.dbo.commonTable
      WHERE key_col = B.key_col);

  DELETE A
    FROM Adb.dbo.commonTable AS A
    LEFT OUTER JOIN Bdb.dbo.commonTable AS B
    ON A.key_col = B.key_col
    WHERE B.key_col IS NULL;
END
GO

Now schedule that stored procedure every 15 minutes using a SQL Server Agent job.

Isolation, concurrency, error handling etc. are all additional considerations, but most concerns should be minor if this is the only process that writes to Adb. You might also consider a more concise MERGE command, but the syntax is daunting and it buys you less than you might expect in terms of improving concurrency and eliminating race conditions.

You might also want to consider transactional replication - get rid of this concept of having to do anything manually, and also the idea that data could be up to 15 minutes out of date.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.