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

Answer 1

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

select nrate from db_name.schema_name.employee

---

Thanks, Leks

Answer 2

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

Answer 3

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

 

Answer 4

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.