SQL Server – How to Update One Database from Another

sql server

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?

Best Answer

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.

Related Question