COPYING TABLE FROM ONE DATABASE TO ANOTHER DB

  • HI TO ALL,

    I WANT TO COPY SOME ROWS FROM ONE TABLE IN ONE DATABASE TO ANOTHER DATABASE.

    IS THERE ANY EASY APPROACH TO PERFORM THIS TASK

    THANKS,

    SANDHYA;)

  • sandhyarao49 (10/12/2008)


    HI TO ALL,

    I WANT TO COPY SOME ROWS FROM ONE TABLE IN ONE DATABASE TO ANOTHER DATABASE.

    IS THERE ANY EASY APPROACH TO PERFORM THIS TASK

    THANKS,

    SANDHYA;)

    Something like this:

    INSERT INTO TargetDB.dbo.TargetTBName (col1,col2)

    select colA, colB

    from SourceDB.dbo.SourceTBName

    where Something='Some value'

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Please don't write in all caps.

    The above solution is how I'd do it as well

  • Thanks to All.

    Suppose if i want to copy a table from one server to another server

    what is the best approach to do this task.

    Thanks,

    Sandhya

  • If table structure already exist, use BCP or BULKINSERT command.

    Otherwise use select * into ]

  • sandhyarao49 (10/13/2008)


    Thanks to All.

    Suppose if i want to copy a table from one server to another server

    what is the best approach to do this task.

    Thanks,

    Sandhya

    You can use linked servers, openrowset function, SSIS package, DTS package, replication and maybe other ways. Without knowing what you are trying to do, it is impossible to give you the best way.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • If the process is a routine maybe the best way is SSIS Package and run it whenever you want!

    šŸ˜‰

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Kishore.P (10/13/2008)


    If table structure already exist, use BCP or BULKINSERT command.

    Otherwise use select * into ]

    BCP and BULKINSERT are used to move data between SQL Server and text files. It can't be used to move data between 2 instances of SQL Servers. In order to use BCP or BULKINSERT when moving the data between 2 servers, you'll need to use BCP first to export the data then copy the file to the second server and then run BCP or BULKINSERT to insert the data into the second table. Iā€™m not sure that this will be the best option.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Is this a one time thing or regular? If it's one time, use the data export, and import, wizards.

    You can use those if it's regularly, but I might suggest something else instead. Red Gate (I work for them) has a tool that makes this flawless (Data Compare) if you need to move multiple tables regularly, like QA-> production or vice versa.

  • apply the linked server and use select * into or use open query example:

    select * from openquery(servername,'select * from databasename.dbo.tablename(nolock) )

Viewing 10 posts - 1 through 9 (of 9 total)

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