Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: sql enterprise manager - copy table

    Hi,

    I can't work out how to easily copy a table through sql enterprise manager including all data structure and the data ... I tried exporting one table then importing it into another but it wont transfer any of the actual data. I don't feel like retyping all 6000 records and I need somehow duplicate this table ... any ideas ???????

    Cheers

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: sql enterprise manager - copy table

    Open your table (all rows), open SQL panel, and add the INTO clause. It should look like:

    SELECT *
    INTO OrderCopy
    FROM Orders
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: sql enterprise manager - copy table

    Originally posted by DoktorBlue
    Open your table (all rows), open SQL panel, and add the INTO clause. It should look like:

    SELECT *
    INTO OrderCopy
    FROM Orders
    And if it is from on DB to another it would be

    SELECT *
    INTO <TABLE_CATALOG>.<TABLE_SCHEMA>.Order
    FROM <TABLE_CATALOG>.<TABLE_SCHEMA>.Orders
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    To add to Doc and Jim's posts, the select into command will create a new table for you minus any indexes or constraints. If you are working with SQL 7.0 you will have to have this option explicitally turned on. The select into is nice as it is minimally logged and fast, if you have an existing table that you need to simply add all entries from one table to the existing table you could do

    insert into table select * from table

    This way is logged though so watch your transaction log size.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    These easiest method through Enterprise Manager it to click on Tools/Data Transformation Services and then either either Import or Export, depending on where you are currently connected.

    This will allow you to import data into existing tables, or create destination tables and populate them.

    If you need to do this on a scheduled basis, set up a DTS package.

    blindman

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Blindman, did you understand Trippin's problem? He does not want to export or import data, he just wants a copy of a table within the same database.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    DB: no, he doesn't But he blames others if we dare to deviate from his train of thought...Did I say TRAIN??? Uh-oh...The train is coming, with BM on the front car... BETTER RUN!!!

    Trippin: follow DoktorBlue's suggestion. I never tried it before but it turned out to be THE EASIEST!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •