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).
极端SQL noob在这里。我有两个数据库(Database1,Database2)。每个db都有相同的表。我想用Database1(Database1,TableA - > Database2,TableA)中的TableA中的数据更新Database2中的TableA。
What's the best way to do this?
最好的方法是什么?
2 个解决方案
#1
2
Like this, assuming you use a dbo schema.
像这样,假设您使用dbo架构。
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.
如果传入数据应覆盖旧数据或您正在使用标识字段,则可能需要先截断Database2.dbo.TableA。
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.
如果您需要在表中使用完全相同的ID,则应在运行脚本之前禁用Database2.dbo.Table上的identity属性,然后重新启用它。
#2
1
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.
如果您将此作为一次性数据同步,则在右键单击特定数据库时,SQL Server会在“任务”选项下提供“导入/导出数据”。您可以选择要用作数据源和目标的sql server / database / tables,然后运行该作业。设置此项后,将有机会设置一个选项来清除目标表并插入源中的所有数据。
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.
您也可以安排这些类型的交易按计划运行,但我从未在这方面做过多少工作。
#1
2
Like this, assuming you use a dbo schema.
像这样,假设您使用dbo架构。
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.
如果传入数据应覆盖旧数据或您正在使用标识字段,则可能需要先截断Database2.dbo.TableA。
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.
如果您需要在表中使用完全相同的ID,则应在运行脚本之前禁用Database2.dbo.Table上的identity属性,然后重新启用它。
#2
1
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.
如果您将此作为一次性数据同步,则在右键单击特定数据库时,SQL Server会在“任务”选项下提供“导入/导出数据”。您可以选择要用作数据源和目标的sql server / database / tables,然后运行该作业。设置此项后,将有机会设置一个选项来清除目标表并插入源中的所有数据。
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.
您也可以安排这些类型的交易按计划运行,但我从未在这方面做过多少工作。