Solved

updating a database

Posted on 1998-12-10
4
165 Views
Last Modified: 2010-03-19
I'm in the process of updating an older version database to a new one. The old database has different table structures but I know the mapping between them;i.e. I know what is the new field in the new database that corresponds to the old field for each table. Both databases are on MS SQl 6.5, I am trying to get some ideas on what should be the best way to do that, shall I use MS Access in doing that or directly on the SQL. Any hints and examples from past experiences are appreciated.
thanks
0
Comment
Question by:khal
  • 2
4 Comments
 
LVL 3

Expert Comment

by:jjbyers
Comment Utility
I use Access for ease of use in moving data between different databases if the amount of data is small. You can use the Query builder to map fields between databases, it's easier than writing all the sql by hand.

If it's alot of data I still use Access to create the sql statement then paste into isql. You have to tweak the sql a little, but once again it's alot easier.

Hope this helps.
Jeff
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 60 total points
Comment Utility
It depends on the size of data and how easy you want it to be. If you don't need fast perfrmance  you can do it in Access or create SQL in access and do it in isql as suggested by jjbyers. If you have a lot of data to transfer, you should definietely use bcp which will be a bit more difficult to setup ,  but data will be transferred several times faster . Difference in transfer speed between Access and bcp can be >10 times.
0
 

Author Comment

by:khal
Comment Utility
If I decided to use Access, I'm thinking of doing the following:
connect the two databases to Access (maybe Link)
run queries like
insert into newdatabase.tableX
select field1, field3...
from olddatabase.tableY

is there anything else I'm missing?
0
 
LVL 3

Expert Comment

by:jjbyers
Comment Utility
Nope.

Use the link, else you'll end up importing all the data into Access.

But you don't have to write the select statement just bring up the query designer, switch to append query, and map that way.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now