Copy Table in SQL2000

Posted on 2011-10-05
Last Modified: 2012-05-12
I am new to SQL. and this was in place when I started here.
I have a sql2000 database.  It is opened with SQL Server manager 2005.
I have a table that needs to be archived every so often.  The table is called TEST.

I need to copy the entire table ( data and columns) to a table called TEST01.  Then need to erase all data in TEST so it is empty.

Should I rename TEST to TEST01 then copy the table structure to a new table call TEST?
How can I do this and what commands would it be.

Question by:mleidich
    LVL 18

    Expert Comment

    by:Anil Golamari
    If you have Test01 table already created then easy way to copy the data from Test table to Test01 table is creating a DTS package if you don't have SSIS package available. In this way you can schedule this package to run and at the same time one's you copy the whole content to Test01 table you can delete the data from Test table.

    Author Comment

    No TEST01 is not created yet.  I need it to be an exact copy of TEST.
    How would I do this?
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>I need to copy the entire table ( data and columns) to a table called TEST01. <<

    >>Then need to erase all data in TEST so it is empty.<<
    If no Foreign keys: TRUNCATE TABLE TEST

    Author Comment

    where do you type SELECT * INTO?

    Will DELETE TEST  - delete the table?

    Will TRUNCATE erase all data in the table?
    LVL 12

    Expert Comment

    by:Ramkisan Jagtap
    select * into test01 from test

    Author Comment

    I see the table names on the left.  I highlight the name of the table.  I do not see how to type or select the command SELECT from TEST.
    LVL 75

    Accepted Solution

    You will click on New Query on the top left corner below the File menu and then type in the query I posted.

    Read up on DELETE and TRUNCATE TABLE in SQL Server BOL.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    794 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

    18 Experts available now in Live!

    Get 1:1 Help Now