Data migration questions?

[Mervin Williams]

I am about to develop code to migrate data from an old application to our
new one. But first, I have a couple of questions:

First, should I use a DataSet to bring the data down to the local machine
that will run the code and execute the transformation logic from it.
Considerations: The source database is about 100 Gb, and one of the tables
includes an Image column.

Second, will I get better performance by using Stored Procedures or Views.

Thanks in advance,

Mervin Williams

 

[William Vaughn]

Ah, no.
Use SSIS (bulk copy) or the SqlBulkCopy class to import the data into SQL
Server. This will be easy to setup and execute and far, far faster. Remember
to import the data into a "work" table that's not part of the production
schema. After all, this data is evil ("All Data is Evil Until Proven
Innocent"--it's part of the Patriot Act).
Next, use a stored procedure to validate or manipulate the data. Once it's
pure, then you can integrated it into the production database.

Now as to the Image datatype. Anyone who has read my books knows that I'm
not in favor of including BLOBs in the database. Even with the newest 2005
features, it's still faster and simpler to store TEXT, IMAGE and the
VarChar/VarBinary (max) columns in a separate file. I would take this
opportunity to undo that approach.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________