Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KnooKie
Aged Yak Warrior
623 Posts |
 Posted - 2002-01-04 : 07:18:33
|
| We have a SQL7 DB and an Access 97 DB. They hold very similar data covering almost the same areas (i.e. same sort of tables) but have been maintained entirely separately for years (yes it's 2 separate companies DBs that need their data merging together as they have merged).What is the best way to do this. Is DTS easily the best option ? I am inclined to think so but is SQL7 DTS good enough to do the job ?Would DTS only really be viable if say the SQL7 DB was moved to SQL2000 first (don't know if this is budgetable though?).Anyone had experience of doing this sort of thing without using DTS ?Any thoughts a helpPaul |
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-01-04 : 07:51:43
|
| You can use DTS with either SQL7 or 2000.My approach would be to migrate both to a new SQL 2000 system. Migrate the first one, then merge in the second. That way you've got both original systems intact in case the merge doesn't go well. Once the data is merged and tested, point whatever client apps to the new database. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-04 : 18:35:36
|
| Following up on Scott's comments.... If you can't go to SQL 2000 at this point but you already have the SQL 7, then I'd create a NEW SQL 7 database to merge into so, like Scott said, you still have the original in case anything goes wrong. If, for some reason, you still need to access this data from Access, then you can setup LINKED tables from Access to SQL Server and see the data in SQL Server.DTS is a great option for copying the data in from Access, or you could use the Access upsizing wizard/add-in. The Access wizard will preserve foreign keys, indexes, etc. but you'll probably want to build all this in SQL first anyway. DTS will let you do transformations (re-arrange columns, format data, etc.) whereas the Upsizing wizard does not.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
Integrating 2 different databases