SQL Server multiple database update.

Posted on 2007-10-04
Last Modified: 2010-08-05
I have a SQL Server running with several databases on it.

One database runs the application sofware, the other runs the web application.  However, I have data in one databse table that needs to be updated on the other database on a particular its updated.  

Can this be done and how?
Question by:lrbrister
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    > its updated.  
    you will need a trigger on the table that get's updated, to "replicate" the change to the other database/table.

    do you need help with the syntax? what are the tables/changes to be done?
    LVL 14

    Expert Comment

    yes it can be done

    you can write a trigger on your SOURCE database-table and in that trigger write the insert statement that will have the other database table name as

    insert into dbname.dbo.tablename. (colnames) values(the values)

    you can get the values in the TRIGGER using the INSERTED command...this object will have the current record thatis inserted...let me know if you need the syntax also...give me the field names if so...

    Author Comment

      Since you had the first response...

    I have a table that will have at most 3 fields that need to get updated on the other table.

    Both tables have an ID that is the same (It's been manually input but I HAVE written a stored procedure that does the input now using a datasource)

    When this table is updated, it's done so on an aspx page with vb behind that calles a stored procedure.

    I need to update AT THE END of the SP the other table with (something like)

    update db2.tbl1


    LVL 142

    Accepted Solution

    with the trigger:

    USE db1
    CREATE TRIGGER trg_replicate_table
     ON tbl1
      UPDATE t2
         SET field1 = i.field1
             , field2 = i.field2
             , field3 = i.field3
       FROM db2.dbo.tbl2 t2
       JOIN inserted i
           ON =


    you could, of course, also modify the procedure, but that risks to get inconsistencies when someone can update the table without the procedure.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now