InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 16:06:46 GMT20http://www.sqlservercentral.com/Forums/Topic769439-361-1.aspxHiyou can schedule the job which will execute after every 1 hour to synchronise database.For synchronising as Lowell has suggested, a trigger can update date column or a flag which will mark records for synchronisation. Later your jobs picks up those records and reset the flag.In a way it is doing replication.Thanks,RupashriMon, 17 Aug 2009 23:39:32 GMTRupashri Gulawanihttp://www.sqlservercentral.com/Forums/Topic769439-361-1.aspxHi Lowell,Thanks for the sample script :-DLet me ask one thing?...using the sample script you gave me how it will be used? I mean is like, for example i have a windows application that will insert data in a database then using the script you gave me it will automatically insert the data to another database or do i need to run it again in order to cascade the inserted data from one database to another database? base on my understanding, correct me if i am wrong:-P i need to run it every time I insert/update data from one database in order to cascade to another database...BTW, thanks for the sample scripts it serves as additional knowledge of my closet:cool:ThanksWed, 12 Aug 2009 13:12:39 GMT..+ hunter +..http://www.sqlservercentral.com/Forums/Topic769439-361-1.aspx[quote][b]..+ hunter +.. (8/12/2009)[/b][hr]Hi LowellThanks for a quick response, Actually my main goal here is I have two database in where it can be place in the same server (what we call a single server) or in a different server (this is what we call a Linked Server) in where if i Insert/Update/delete data in one database it will cascade in the another database which is perfectly working using a trigger in a single server but not in a linked server.This two database are in different database schema so what i did is I make sure that the column available in the list of table in the first database is properly mapped in the table inside the next database. So what am i asking is like what you have said am i asking for strategies?Absolutely yes, I am asking for another strategies in where i can synchronized my two database without or aside from using a trigger?...[/quote]Based on this description is sounds like a perfect use case for Replication.Wed, 12 Aug 2009 12:59:04 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic769439-361-1.aspxthe new 2008 booksonline has a lot of stuff on MERGE and EXCEPT[url]http://technet.microsoft.com/en-us/library/bb510625.aspx[/url]the neat thing is how it has clauses for WHEN MATCHED and NOT MATCHED:, when the merge finds a match, based on the criteria, you know you need to update, if no match was found it must be new[code]USE AdventureWorks;GO-- Create a temporary table variable to hold the output actions.DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));MERGE INTO Sales.SalesReason AS TargetUSING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion')) AS Source (NewName, NewReasonType)ON Target.Name = Source.NewNameWHEN MATCHED THEN UPDATE SET ReasonType = Source.NewReasonTypeWHEN NOT MATCHED BY TARGET THEN INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)OUTPUT $action INTO @SummaryOfChanges;-- Query the results of the table variable.SELECT Change, COUNT(*) AS CountPerChangeFROM @SummaryOfChangesGROUP BY Change;[/code]Wed, 12 Aug 2009 12:52:24 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic769439-361-1.aspxHi LowellThanks for a quick response, Actually my main goal here is I have two database in where it can be place in the same server (what we call a single server) or in a different server (this is what we call a Linked Server) in where if i Insert/Update/delete data in one database it will cascade in the another database which is perfectly working using a trigger in a single server but not in a linked server.This two database are in different database schema so what i did is I make sure that the column available in the list of table in the first database is properly mapped in the table inside the next database. So what am i asking is like what you have said am i asking for strategies?Absolutely yes, I am asking for another strategies in where i can synchronized my two database without or aside from using a trigger?I have noticed also that u said "SQL has the new "upsert" ability using the MERGE command, that is naother option, and that would not require a trigger." an you give me some example?I will really appreciate it. :-)Regards,Hamtaro[quote][b]Lowell (8/12/2009)[/b][hr]well if you are doing this to improve your understanding of how SQL works, I applaud you; sometimes reinventing the wheel can be a very rewarding experience education wise.practically speaking, however, you'd usually use the built in tools SQL server can provide, for example replication, snapshot replication and mirroring, it really depends on your requirements.you didn't really as a question...just made a statement on what you are planning to do; did you have a question or were looking for strategies?triggers and linked servers are one way to do it; the way i've done it was by adding columns for last_updated to every table I cared to track, and the triggers to update that last_updated column during an UPDATE...then you can just scan for rows that have changed since the last time you synced them.In my situation, we didn't need to track DELETED data, but I'd like to hear your strategy for when a row gets deleted...in or case we didn't want the changes propigated if deleted, so we kind of had the last updated value forever.SQL has the new "upsert" ability using the MERGE command, that is naother option, and that would not require a trigger.[/quote]Wed, 12 Aug 2009 10:28:26 GMT..+ hunter +..http://www.sqlservercentral.com/Forums/Topic769439-361-1.aspxwell if you are doing this to improve your understanding of how SQL works, I applaud you; sometimes reinventing the wheel can be a very rewarding experience education wise.practically speaking, however, you'd usually use the built in tools SQL server can provide, for example replication, snapshot replication and mirroring, it really depends on your requirements.you didn't really as a question...just made a statement on what you are planning to do; did you have a question or were looking for strategies?triggers and linked servers are one way to do it; the way i've done it was by adding columns for last_updated to every table I cared to track, and the triggers to update that last_updated column during an UPDATE...then you can just scan for rows that have changed since the last time you synced them.In my situation, we didn't need to track DELETED data, but I'd like to hear your strategy for when a row gets deleted...in or case we didn't want the changes propigated if deleted, so we kind of had the last updated value forever.SQL has the new "upsert" ability using the MERGE command, that is naother option, and that would not require a trigger.Wed, 12 Aug 2009 10:10:54 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic769439-361-1.aspxHi GuysI am currently developing an application that has a capability to synchronized two database at first i try it using a trigger. You have any idea on how can i synchronized my two database without using a trigger? why am i asking this? due to some issue in using a trigger in a linked server :crying:ThanksWed, 12 Aug 2009 09:43:14 GMT..+ hunter +..