Possible Duplicate:
update one table with data from another
Extreme SQL noob here. I have two databases (Database1, Database2). Each db has the same tables. I want to update TableA in Database2 with the data in TableA from Database1 (Database1,TableA -> Database2,TableA).
What's the best way to do this?
Like this, assuming you use a dbo schema.
insert into Database2.dbo.TableA (column1, column2, etc)
select column1, column2, etc
from Database1.dbo.TableA
You might want to truncate Database2.dbo.TableA first if the incoming data should overwrite the old or you are using an identity field.
If you need the ids to be the exact same across the tables, you should disable the identity property on Database2.dbo.Table before running the script and re-enable it afterward.
If you are doing this as a one-time data sync, SQL Server offers an Import/Export Data under the Tasks option when you right-click on a particular database. You can select the sql server/database/tables to be used as a data source and destination and then run the job. When you set this up, there will be an opportunity to set an option to clear the destination table and insert all the data from the source.
You can also schedule these types of transactions to be run on a scheduled basis, though I have never done much work in this area.
