Post reply

COPYING TABLE FROM ONE DATABASE TO ANOTHER DB

  • October 12, 2008 at 4:10 am

    #96755

    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;)

  • October 12, 2008 at 4:14 am

    #883873

    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/

  • October 12, 2008 at 9:01 am

    #883895

    Please don't write in all caps.

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

  • October 13, 2008 at 4:58 am

    #884063

    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

  • October 13, 2008 at 5:20 am

    #884073

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

    Otherwise use select * into ]

  • October 13, 2008 at 6:05 am

    #884102

    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/

  • October 13, 2008 at 6:11 am

    #884106

    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]

  • October 13, 2008 at 6:12 am

    #884107

    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/

  • October 13, 2008 at 8:21 am

    #884162

    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.

  • October 14, 2008 at 2:48 am

    #884474

    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