How do I bulk-copy from SQL 2000 to SQL 2008, while updating schema changes on the fly?

Posted on 2009-05-01
Last Modified: 2013-11-30
We have a SQL 2000 database and a SQL 2008 data warehouse. The first must be copied each night to the second. Sometimes small changes are done on the first. We're trying to do a bulk copy that also copies the schema.

Before the 2008 upgrade, we used DTS for that. But now we're using Integration Services. IS seems to be unable to copy the schema like DTS can.

How to do this efficiently and flexibly?
Question by:hvleijen
    1 Comment
    LVL 51

    Accepted Solution

    Well you could still use DTS, there is a download for 2008 for 2005 compatability which includes the DTS packages, so it does seem that MS have realised an ongoing need for DTS support. There is a new feature pack released in April :

    Other wise, maybe a linked server. You can examine the structure of the table, and do data loads but will require some coding effort.

    If the destination table is meant to be as per the source table, then you ccould also use the export wizard to create the "replacement" table...

    Or there are a few third party tools as well...

    If you could give an indication on a preferred approach then can give more explicit examples if needed.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how the fundamental information of how to create a table.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now