This isn't ColdFusion specific but I figure it's worth asking here - I have two SQL 2005 servers that I am synchronizing data between using stored procedures. On one server, MAIN, I have 5 databases with identical structure that feed a database of UNION based views. On the other server, WEB, there is one database that, for about 30 of the almost 1500 tables (I didn't create the data schema nor do I have any ability to change it, it's a Microsoft product that we're reporting on) in the MAIN db structure have a similarly named table, something along these lines:
MAIN table - UPR00100 WEB table dUPR00100 Every 6 hours a stored procedure on WEB fires off that does the following: TRUNCATE TABLE dUPR00100 INSERT INTO dUPR00100 (column list) SELECT (column list) FROM MAIN.Reporting.dbo.UPR00100 I'm wondering two things: 1. Is there a better way to do this data sync outside of using the stored procedure approach? The rule for the sync is that all data on MAIN wins... and there is no code on WEB that modifies the tables on WEB. 2. Would there be any kind of time and process savings to rewrite the procedures to perform and insert/update/delete process instead of always wiping and reloading? My limitations are this - I can't upgrade the database server and I can't change any of the table structures. I can only work with the synchronization functionality. Thanks! Until Later! C. Hatton Humphrey http://www.eastcoastconservative.com No trees were killed in the sending of this message, but a large number of electrons were terribly inconvenienced. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346421 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
