CODEWITHSUNDEEP
sqlsql-servertriggersreplication
beebul
beebul

Reputation: 1003

SQL Server - mirror some columns from tables to another database on the same server without replication

I have a SQL Server 2012 Web Edition (11.0.5058.0) instance on a VPS which hosts two databases. I would like to mirror a couple of columns from 3 tables to the second database, but I don't have transactional replication installed.

So I have a Staff table on the source database - I just want the staff_code and unique_id - I have an Activity table - I just need the activity_code, description and unique_id.. etc.

What is the best way to go about this - would that be triggers? The data is not regularly updated, possibly once a week - but I would still like the synchronisation to be fast if possible?

The data in the source database may be deleted, updated or inserted, by another application, so I want to ensure the data in my database reflects that information correctly.

Thanks for any suggestions!

UPDATED: Table comparison example:

SELECT CASE WHEN NOT EXISTS
     ( SELECT [COLUMN1],[COLUMN2],[UNIQUE_ID] FROM [SOURCE-DATABASE].[dbo].[SOURCE-TABLE]
       EXCEPT 
       SELECT [COLUMN1],[COLUMN2],[UNIQUE_ID] FROM [DESTINATION-DATABASE].[dbo].[DESTINATION-TABLE]
     ) 
         AND NOT EXISTS 
     ( SELECT [COLUMN1],[COLUMN2],[UNIQUE_ID] FROM [DESTINATION-DATABASE].[dbo].[DESTINATION-TABLE]
       EXCEPT 
       SELECT [COLUMN1],[COLUMN2],[UNIQUE_ID] FROM [SOURCE-DATABASE].[dbo].[SOURCE-TABLE]
     ) 
     THEN 'True' 
     ELSE 'False' //GRAB NEW OR UPDATED DATA
   END AS result ;

Upvotes: 2

Views: 6217

Answers (1)

marc_s
marc_s

Reputation: 755043

As long as the two databases can be connected (e.g. can you do a SELECT * FROM SecondDB.dbo.Activity?), then I would just

  • set up a query (stand-alone, or in a stored procedure) that just checks whether or not the data on the source has changed

  • updates the second database using normal SELECT, INSERT, UPDATE and possibly DELETE statements

  • set up that query/stored procedure with a SQL Server Agent Job to run at regular intervals, e.g. once every night, once every week - whatever works for you

I don't think triggers would be a good choice here - triggers should be kept very small, lean, fast - and "replicating" to another database sounds like too much processing work for a nimble trigger.... (also if you triggers take a long time to complete, the calling application will have to wait for that whole time..... not good for your application performance!)

Upvotes: 2

Related Questions