Solved

updating a database

Posted on 1998-12-10
4
175 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 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Viewers will learn how the fundamental information of how to create a table.

630 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