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!

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

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).

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

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!

Wrox SQL Server 2000 DTS :) if you can still find it!

Thanks!

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

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