locked
Joining tables from two different servers RRS feed

  • Question

  • I want to join two tables present on different servers and insert the data into a table  on my server.
    can someone please help me with the same (with an example if possible)?

    INSERT INTO MYDB.MYTABLE
    SELECT *
    FROM SERVER1DB.SERVER1TABLE AS booked
    INNER JOIN SERVER2DB.SERVER2TABLE@REMOTEDB as agent
    ON agent.AGENT_ID = booked.agent_id

    (where @REMOTEDB  is the connection between server1 and server2)
    • Moved by VMazur Wednesday, July 29, 2009 6:57 PM (From:ADO.NET Data Providers)
    Wednesday, July 29, 2009 1:23 PM

Answers

All replies

  • Hi,
    I am a bit confused about your question.
    Do you want query within the two tables of two different databases of the same database server or 
    you want to use two different database servers ?

    This will help to answer.
    Wednesday, July 29, 2009 1:28 PM
  • Maybe you could indicate what type of database(s) you are working with?
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, July 29, 2009 2:05 PM
  • You will want to create a linked server to REMOTEDB.  See http://msdn.microsoft.com/en-us/library/ms188279.aspx .

    You can then refer to tables on you remote server by [SERVER_NAME].[DATABASE_NAME].[SCHEMA_NAME].[TABLE_NAME] or setup an alias for objects on the linked server.

    • Marked as answer by Zongqing Li Thursday, August 6, 2009 3:31 AM
    Monday, August 3, 2009 1:40 PM