Solved

updating a database

Posted on 1998-12-10
4
166 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
ID: 1092200
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
ID: 1092201
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
ID: 1092202
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
ID: 1092203
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

20 Experts available now in Live!

Get 1:1 Help Now