Re: Data migration questions?

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)
____________________________________________________________________________________________
"Mervin Williams" <mervinwilliams@xxxxxxxx> wrote in message news:uy4WiZtfIHA.4396@xxxxxxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • RE: xp_cmdshell, ownership chaining, sql 2000
    ... Cross database ownership chaining enabled ... The procedure is called by a crystal report. ... If I log in to SQL Server through SSMS 2005 using the same user as the ... I get the following error when attempting to execute ...
    (microsoft.public.sqlserver.programming)
  • xp_cmdshell, ownership chaining, sql 2000
    ... Cross database ownership chaining enabled ... The procedure is called by a crystal report. ... If I log in to SQL Server through SSMS 2005 using the same user as the ... I get the following error when attempting to execute ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with bulk load security.
    ... Then I defined the procedure using 'with execute as self'. ... database is trustworthy, you cannot get rights outside the database. ... One is to make the database trustworthy, ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)