Solved

updating a database

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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.
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 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