i know this should be db 101, but its just not as clear as it can be for me. I am using SQL2005 express and i want to copy data from databaseA to databaseB. DatabaseB already contains existing data - it may even contain data with pk's that have changed or dont exist - for example:
我知道这应该是db 101,但它对我来说并不是那么清楚。我正在使用SQL2005 express,我想将数据从databaseA复制到databaseB。 DatabaseB已经包含现有数据 - 它甚至可能包含pk已更改或不存在的数据 - 例如:
DataBase A pk1 = peaches pk2 = apples
数据库A pk1 =桃子pk2 =苹果
DataBase B pk1 = peaches pk2 = oranges pk3 = apples
数据库B pk1 =桃子pk2 =橙子pk3 =苹果
now both A & B have related tables that are tied to the pk.
现在,A和B都有与pk相关的相关表。
Historically i would write an app that selects the data from A and copies it to B via its Insert/Update procs (using .NET), but obv this is very cumbersome, tho some advantages are i could have a dropdown that allows you to select A to copy - and B to copy to if it exists, or say add new - for instance:
从历史上看,我会编写一个应用程序,从A中选择数据并通过其插入/更新过程(使用.NET)将其复制到B,但是这很麻烦,有些优点是我可以有一个下拉列表,允许您选择A要复制 - 和B要复制到它是否存在,或者说添加新 - 例如:
(dropdown - select source) Peaches Apples
(下拉菜单 - 选择来源)桃子苹果
(dropdown - select target) -new- peaches oranges apples
(下拉菜单 - 选择目标) - 新桃子橙子苹果
I was going to use SSIS, but the target db has only SQL express so this is not available nor do i have time to learn it (ive used dts much in the past, but not this newer tool). In SQL2000 i would just use DTS, but with the more complicated schema now im not even sure i would trust that.
我打算使用SSIS,但目标DB只的SQL Express,所以这是不可用也没有,我有时间去学习它(IVE使用DTS多过去,但不是这个新工具)。在SQL2000中我只会使用DTS,但现在使用更复杂的架构我甚至不确定我会信任它。
Any suggestions or should i just continue to write my custom apps to migrate data from A to B?
任何建议或我应该继续编写自定义应用程序以将数据从A迁移到B?
Im looking forward to everyones suggestions - i would love to continue down a path that i feel is the best way to do this :-)
我期待着每个人的建议 - 我希望继续沿着一条路走,我觉得这是最好的方式:-)
4 个解决方案
#1
1
if the servers are on the same network add a linked server (look up in transact sql books online). Then you can run queries across the two servers.
如果服务器在同一网络上,则添加链接服务器(在线查找transact sql书籍)。然后,您可以跨两个服务器运行查询。
SQL Server 2005 Express sucks in that it does not include SSIS. However you can also use BCP (bulk copy, look this up in the transact sql books online as well) to copy your data to different tables on the target server. It is probably in your path so from dos you can type bcp /? to get a list of options. Once the data is in a table on your target server you can run queries against it.
SQL Server 2005 Express很糟糕,它不包含SSIS。但是,您也可以使用BCP(批量复制,也可以在联机交易sql书籍中查找)将数据复制到目标服务器上的不同表中。它可能在你的路径中,所以从dos你可以输入bcp /?获取选项列表。一旦数据在目标服务器上的表中,您就可以对其运行查询。
You do need to create a mapping between keys. I don't know how from peaches/apples you get peaches/oranges/apples. No doubt there is some business logic. Once you link the servers or get the data on one server. You can either use business logic to map old primary keys to new keys. Or a mapping table (oldkey, newkey) or (oldkey1, oldkey2, newkey1, newkey2, newkey3) that you manually fill out.
您需要在键之间创建映射。我不知道如何从桃子/苹果中获得桃子/橙子/苹果。毫无疑问,存在一些商业逻辑。链接服务器或在一台服务器上获取数据后。您可以使用业务逻辑将旧主键映射到新键。或者手动填写的映射表(oldkey,newkey)或(oldkey1,oldkey2,newkey1,newkey2,newkey3)。
#2
1
sql 2008 has an interesting feature called Change Data Capture that might be interesting down the road for you http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
sql 2008有一个名为Change Data Capture的有趣功能,对你来说可能很有意思http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
#3
0
Not real familiar with the limitations of SQL Server Express, but could you backup? And then import as another database?
不是很熟悉SQL Server Express的限制,但你可以备份吗?然后导入另一个数据库?
#4
0
You might look into merge replication - http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx
你可能会考虑合并复制 - http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx
Is it always a one way push? A->B?
它总是单向推动吗? A-> B?
As a note, if you could get SSIS, you should take a little time to learn it. It's better than DTS imho.
作为一个注释,如果你能得到SSIS,你应该花一点时间来学习它。它比DTS imho更好。
#1
1
if the servers are on the same network add a linked server (look up in transact sql books online). Then you can run queries across the two servers.
如果服务器在同一网络上,则添加链接服务器(在线查找transact sql书籍)。然后,您可以跨两个服务器运行查询。
SQL Server 2005 Express sucks in that it does not include SSIS. However you can also use BCP (bulk copy, look this up in the transact sql books online as well) to copy your data to different tables on the target server. It is probably in your path so from dos you can type bcp /? to get a list of options. Once the data is in a table on your target server you can run queries against it.
SQL Server 2005 Express很糟糕,它不包含SSIS。但是,您也可以使用BCP(批量复制,也可以在联机交易sql书籍中查找)将数据复制到目标服务器上的不同表中。它可能在你的路径中,所以从dos你可以输入bcp /?获取选项列表。一旦数据在目标服务器上的表中,您就可以对其运行查询。
You do need to create a mapping between keys. I don't know how from peaches/apples you get peaches/oranges/apples. No doubt there is some business logic. Once you link the servers or get the data on one server. You can either use business logic to map old primary keys to new keys. Or a mapping table (oldkey, newkey) or (oldkey1, oldkey2, newkey1, newkey2, newkey3) that you manually fill out.
您需要在键之间创建映射。我不知道如何从桃子/苹果中获得桃子/橙子/苹果。毫无疑问,存在一些商业逻辑。链接服务器或在一台服务器上获取数据后。您可以使用业务逻辑将旧主键映射到新键。或者手动填写的映射表(oldkey,newkey)或(oldkey1,oldkey2,newkey1,newkey2,newkey3)。
#2
1
sql 2008 has an interesting feature called Change Data Capture that might be interesting down the road for you http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
sql 2008有一个名为Change Data Capture的有趣功能,对你来说可能很有意思http://blog.benhall.me.uk/2007/06/sql-server-2008-change-data-capture-cdc.html
#3
0
Not real familiar with the limitations of SQL Server Express, but could you backup? And then import as another database?
不是很熟悉SQL Server Express的限制,但你可以备份吗?然后导入另一个数据库?
#4
0
You might look into merge replication - http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx
你可能会考虑合并复制 - http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx
Is it always a one way push? A->B?
它总是单向推动吗? A-> B?
As a note, if you could get SSIS, you should take a little time to learn it. It's better than DTS imho.
作为一个注释,如果你能得到SSIS,你应该花一点时间来学习它。它比DTS imho更好。