?
Solved

updating a database

Posted on 1998-12-10
4
Medium Priority
?
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 240 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

718 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