none
How to update remote table using openquery

    Question

  •  

     

    When I run this code:

    update openquery(securi01,'select nrate from employee') set
    nrate = RATES.nrate from RATES inner join employee on employee.cempid = RATES.cempid

    I get this error:

    Msg 208, Level 16, State 1, Line 1

    Invalid object name 'employee'.

     


    Why doesn't this code work? I'm not sure if it can actually work but I can't find any useful information that helps me understand the OPENQUERY function so I'm hoping someone here can help

    • Moved by Tom PhillipsModerator Friday, January 08, 2010 2:59 PM TSQL Question (From:SQL Server Database Engine)
    Thursday, January 07, 2010 8:29 PM

Answers

All replies

  • Instead of using only the table name , try specifying the multipart name here

    select nrate from db_name.schema_name.employee


    Thanks, Leks
    Thursday, January 07, 2010 8:40 PM
  • You can follow the syntax below. Let us know if helpful.

    -- T-SQL OPENQUERY UPDATE remote table 
    UPDATE oq 
    SET    oq.ListPrice = p.ListPrice 
    FROM   OPENQUERY([LINKEDSERVER],
           'select ProductID, ListPrice from tempdb.dbo.Product') oq 
           INNER JOIN AdventureWorks2008.Production.Product p 
             ON p.ProductID = oq.ProductID 

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    Saturday, January 09, 2010 7:15 AM
    Moderator
  • Hi Kalman,

    i've tried your solution on a Sql2k and i'm geting the following error

    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'oq'.

    Is this syntax valid only on non 2k server? I don't think that i can use an alias with "from table" or am i missing something? I'm having troubles updating remote table on a linked server and with usual syntax, it's waaay to slow. Now, with openquery i'm almost there, but the last step is the toughest. I cannot update the remote table via openquery in an efficient way. Your solution looks very promising, if it only worked.

    Thanks

     

    Tuesday, October 25, 2011 7:18 PM
  • You can follow the syntax below. Let us know if helpful.

    -- T-SQL OPENQUERY UPDATE remote table 
    UPDATE oq 
    SET    oq.ListPrice = p.ListPrice 
    FROM   OPENQUERY([LINKEDSERVER],
           'select ProductID, ListPrice from tempdb.dbo.Product') oq 
           INNER JOIN AdventureWorks2008.Production.Product p 
             ON p.ProductID = oq.ProductID 

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    Following this sintax works for me in SQL2008, thanks.
    Friday, June 15, 2012 4:53 PM