Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Server multiple database update.

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 table...as its updated.  

Can this be done and how?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

>...as 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?
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...
Avatar of Larry Brister

ASKER

angelIII:
  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)


begin
update db2.tbl1
set
db2.tbl2.field1=db1.tbl1.field1,
db2.tbl2.field2=db1.tbl1.field2,
db2.tbl2.field3=db1.tbl1.field3

where
db2.tbl2.ID=db1.tbl1.ID


ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account