none
How to transfer data from one table in a database to another table in different database ?

Answers

  • You simply use an INSERT...SELECT statement and reference the tables by three part name notation prefixed by database name and schema name. Here is example (note here the 'dbo' schema is used):

    INSERT INTO DestinationDB.dbo.DestinationTable (col1, col2, col3)
    SELECT col1, col2, col3
    FROM SourceDB.dbo.SourceTable;

    Plamen Ratchev
    • Marked as answer by Zongqing Li Friday, August 28, 2009 8:04 AM
    Saturday, August 22, 2009 5:28 AM
    Moderator

All replies

  • You simply use an INSERT...SELECT statement and reference the tables by three part name notation prefixed by database name and schema name. Here is example (note here the 'dbo' schema is used):

    INSERT INTO DestinationDB.dbo.DestinationTable (col1, col2, col3)
    SELECT col1, col2, col3
    FROM SourceDB.dbo.SourceTable;

    Plamen Ratchev
    • Marked as answer by Zongqing Li Friday, August 28, 2009 8:04 AM
    Saturday, August 22, 2009 5:28 AM
    Moderator
  • Hi Kiran may be like this you can use opendatasource...

    SELECT * into Table FROM OPENDATASOURCE( ‘SQLOLEDB’, ‘Data Source=Servername IP\Name;User ID=USERNAME; Password= PASSWORD’ ).fullyqualifiedtablepath(db.owner.table)

    This will create a new table in DB and if you want to insert onto already present table then use insert into in place of into with the specific columns.
    May be helps you out.
    Thanks...
    Saturday, August 22, 2009 6:59 AM