Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 4th, 2003, 10:25 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default Data Transfer

Hi,
Does anyone know whats the best way to transfer data from one database to another in SQL Server(besides DTS). The destination database has no schema defined so far. Goal is to write a program (VB.NET) to customize this transfer. Any ideas, thoughts will be really appreciated. Thanks!
  #2 (permalink)  
Old June 4th, 2003, 11:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Why is DTS not an option? It's probably the best way to do what you want.

Without a schema you could use SELECT INTO statements to create the output tables, whch may or may not set them up correctly, since datatypes of columns in the new table are inferred.

BACKUP/RESTORE might work for you to transfer the entire database.

You can use BCP to transfer data to a file, then from a file to another database, but a schema is ultimately needed.

A schema is ultimately needed anyway, as you'll need indexes, unique and DRI constraints, etc. ...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #3 (permalink)  
Old June 4th, 2003, 11:19 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi jeff,
Thanks for replying. Problem is that the new database wont have one to one column mapping with the old one. We are going to upgrade the old database and new one will be created from scratch. Its not even sure if same table from input will map to same table in output database. Thats why i was wondering if there is a way to write a program which will grab the schema of new database(once its schema is decided) and give it as input to the program and move data. Is it even feasible? I guess someone will have to tell the program to map which columns from old database to new one? DTS can be an option by programming it through VB.NET. Any other suggestions (e.g XML file for column mapping or something like that). I was thinking like a windows service which can run on server along with DTS package and XML column mapping but then how do i copy relationships (one-to-many etc).
  #4 (permalink)  
Old June 4th, 2003, 11:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Then I say for sure DTS is the best way to deal with this, since it allows you to easily set up transformations between source and destination columns.

I'm not aware of any programs which can perform the sort of magic you'd like - somebody is going to have to tell something how to get from Database A to Database B if the mapping is not one-to-one. Thus ultimately it will require both source and destination schemas, and a statement of the rules to employ to map one to the other. DTS does this.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #5 (permalink)  
Old June 4th, 2003, 11:54 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok, thanks! I wanted to make sure that this was the right approach. Do you know any good books regarding DTS which give detailed info about invoking/customizing it through a programming language? Many thanks!
  #6 (permalink)  
Old June 4th, 2003, 11:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 132
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wrox SQL Server 2000 DTS :) if you can still find it!
  #7 (permalink)  
Old June 4th, 2003, 12:06 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by chrislepingwell
 Wrox SQL Server 2000 DTS :) if you can still find it!
Thanks!
  #8 (permalink)  
Old June 6th, 2003, 07:38 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Guys,
Does anyone know whats the best way to define rules which can be used to map data from one database to another one? Could it be an XML file mapping which shows what columns map to what? But then it would probably get too big because the input database is huge. Also one column could be split into many or many into one based on final destined schema (which is not in place yet). DTS will be used at the back end to move data but i am a little unclear about the mapping architecture. Any ideas, suggestions. Thanks!

Paul
  #9 (permalink)  
Old June 9th, 2003, 06:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm a little confused about your question. Why do you need to create the 'architecture' for the mapping? Are you concerned about documenting the mapping, or about actually moving the data. If the second, DTS does it all. Build a few test packages.

regards
David Cameron


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transfer data from one workbook into another mona_upm84 Excel VBA 0 September 3rd, 2008 11:32 PM
DATA TRANSFER vish_vj ADO.NET 1 March 7th, 2008 01:16 PM
Data Transfer Problem in C# akumarp2p C# 0 December 5th, 2006 11:51 AM
Data Transfer problem in C# akumarp2p C# 0 December 5th, 2006 10:38 AM
Transfer data mepancha SQL Server 2000 4 March 24th, 2005 04:29 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.