Export contents of SQL 2000 table to overwrite another table?

Posted on 2009-04-23
Last Modified: 2012-05-06
I'm running SQL Server 2000 on a Windows Server 2003 machine, and I've got multiple databases set up, some of which are clones.

As of sometime last week, one of the tables in our primary database was damaged, and I'm trying to export the data from it's clone to that table. I want to go from one database table to another database table, copying the data but retaining the destination table's identification. Basically I just need to replace the damaged data with good data.

I'd prefer to use the Import/Export wizard if possible, as I'm not very good at SQL statements. Any help would be appreciated.

Question by:SysAdmin06
    LVL 6

    Expert Comment

    I do not remember the SQL server 2000 management tool, but in SQL Server 2005 Management Studio there is an option for "Generate Script" by right clicking on a database. There you decide the options and what table you want to include. I am sure something similar in 2000.

    Author Comment


    There is, thank you, but what do I do from there? I'm trying to copy the table SYACTFIL_SQL in the database data_001 ---> the table SYACTFIL_SQL in the database DATA_100. What kind of script do I generate? Insert, Select, Update, etc? When I choose one of those options, how do I write the script?
    LVL 6

    Accepted Solution

    There may be 2 steps involved in process, depending on the corruption of the primary table.
    Depending on weather you want to keep or not some data from the corrupt database there are 2 directions:

    If the table and data is corrupt you will need to recreate the table structure again using Enterprise Manager to generate the TABLE CREATE script

    If the table is intact, just the data is corrupt you need to use the Bulk Copy Program, a command line utility to dump the data from the "good" db to the "corrupt" db.

    Some instructions on that are here:

    There is also a possibility to transfer data between 2 tables of separate databases, see links, just make sure you have backups before you test:


    Author Comment

    Here's a script I borrowed from someone else and modified for my tables:

    USE [data_001]

    SELECT *
    INTO [DATA_100].[dbo].[SYACTFIL_SQL]

    That claims to run successfully, but when I check the data in the destination table, it's still unchanged. Mainly, I'm trying to fill three columns of data that are currently filled with zeroes, with the data from the columns of the other table. After I run this, the destination table still has zeroes. Is there something I have to add to overwrite data?
    LVL 6

    Expert Comment

    The SELECT statement does not change anything, you need to use either INSTERT or most likely UPDATE.

    Check this link for more info:


    Author Comment

    Thanks for the help, Brainstormer. While researching, I found this:

    You have to buy the tools individually, which is unfortunate, but their Copy Table tool is one of the easiest pieces of software I've seen. Enter source DB, source table, destination DB, destination table, go through a few very simple options and you're done. It worked perfectly, and saved me a ton of time and stress trying to figure everything out on my own.

    Thanks for the great tips though!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    761 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

    14 Experts available now in Live!

    Get 1:1 Help Now