Post reply

copy tables from 1 db to other db

  • March 7, 2007 at 9:59 pm

    #90962

    Hello everyone i m using SQL Server 2000, i want to copy tables with data from one Database to other in the same server. Kindly tell me is this possible with the DTS as in my 2nd database, there is no table, i want to copy from 1 db to other but not all tables, i need some tables.

    Kindly reply me how i do this.

  • March 8, 2007 at 1:32 am

    #693728

    The quickest way to do this (but I'm fairly sure not the most efficient) would be to do the following:

    Create the table in your target database (right click the table in SSMS and script as create and change names etc/constraints as needed). Then you can run somthing like the following:

    USE [TargetDB];

    INSERT INTO [TargetDB]..[TargetTable] SELECT * FROM [SourceDB]..[SourceTable]

    you might need to specify the names of the columns explicity if you have an identity columns.

    HTH,

    - James

    --
    James Moore
    Red Gate Software Ltd

  • March 8, 2007 at 9:54 am

    #693870

    Even quicker, use SELECT INTO:

    SELECT sourcetable.*

    INTO targetdb..targettable

    FROM sourcetable

    To answer the original question, yes, DTS can copy any or all tables from one database to another.  Just use the import/export wizard.

    Greg

     

    Greg

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply