Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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!
Reply With Quote
  #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
Reply With Quote
  #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).
Reply With Quote
  #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
Reply With Quote
  #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!
Reply With Quote
  #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!
Reply With Quote
  #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!
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 09:03 PM.


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