Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Question Unanswered: How can I create a temp table on a linked server?

    Hello

    I have a local SQL2005 server with a linked SQL2000 server. I would like to know how to create a temporary table in the remote server in such a way that I can make an inner join as follows; my idea is to optimized a distributed query by doing so:

    create table #myRemoteTempTable

    insert into #myRemoteTempTable
    select * from myLocalTable

    update myRemoteTable
    set
    Value=#myRemoteTempTable.Value
    from myRemoteTable
    inner join #myRemoteTempTable on #myRemoteTempTable.ID=myRemoteTable.ID

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    What you're looking for is Linked Servers. This page at Microsoft is a good starting point. It has some pretty useful links at the very bottom.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Mar 2003
    Posts
    144

    Question

    Thanks for your reply.

    If I have a linked server object, can I used some like this?:

    create table #MyLinkedServer.MyRemoteDB..myTempTable
    (
    some columns...
    )

  4. #4
    Join Date
    Mar 2003
    Posts
    144
    I figured out how to create the remote temp table and an index, but it takes more than a minute just to insert about 20,000 rows on that table; is not that too much? what can I do?

    CREATE TABLE myLinkedServer.#myRemoteTempTable(
    Product_ID varchar(20) NOT NULL,
    Forecast numeric(18, 0) NULL,
    Note varchar(8000) NULL,
    Date_ID datetime NOT NULL
    )

    CREATE UNIQUE CLUSTERED INDEX #myRemoteIndex ON #myRemoteTempTable(Product_ID, Date_ID)

    insert into myLinkedServer.#myRemoteTempTable
    select
    Product_ID,
    Forecast,
    Note,
    Date_ID
    from myLocalTable
    where UpdateTimeStamp>@LastUpdate

  5. #5
    Join Date
    Mar 2003
    Posts
    144

    Exclamation The temporary table is created in the TempDB database

    Even that I use the sentence CREATE TABLE myLinkedServer.#myRemoteTempTable, I have just discovered by looking at the estimated query plan, that the table is actually created in the TempDB database. Why it is not created in the remote server?

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Aren't you going to be better off creating a stored procedure on your linked server and then calling it (the sp) from the local machine?

    Remember, there are things that you can do and there are things that you should do. And the two are not always the same.

    Regards,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Mar 2003
    Posts
    144

    Lightbulb

    I came up with the following solution:

    I created a regular table and a clustered index for that table in the remote server and declared a synonym in the local DB that represent that table, and then filled that table like so:

    insert into synMyTempRemoteTable
    select
    Product_ID,
    Forecast,
    Note,
    Date_ID
    from myLocalTable
    where UpdateTimeStamp>@LastUpdate

    However, it takes 19 seconds to insert 21,000 rows in this table :-(

    And then I call a remote stored procedure that makes the desired update in the remote table by joining that table with MyTempRemoteTable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •