Update Local Table With Data From Linked Server Using Open Query

Question

This is the syntax I am trying to use, but I get an error of

Incorrect syntax near #

I have also tried it with an actual table and not a temp table and still not succesful. Here is my syntax, what should I do in order to be able to update a table on my SQL Server 2008 installation with data from a linked connection to postgresql?

Create Table #Test (field1 varchar(100), field2 varchar(100))

DECLARE @salesname nvarchar(500) = N'''Sam Wise''';
DECLARE @d1 nvarchar(100) = N'05/01/2016';
DECLARE @d2 nvarchar(100) = N'05/31/2016';
DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(LinkedServer, 
''UPDATE #Test
SET field1 = a.salesname SalesName
,field2 = b.salesTotal TotalSales
from sales a
inner join empinfo b
on a.empid = b.empid
WHERE a.salesname IN ('+ REPLACE(@salesname, '''', '''''') + N')
AND iv.jbinvoicedate BETWEEN ('+ CHAR(39) + CHAR(39)
+ CAST(@d1 AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@d2 AS NVARCHAR(500)) + CHAR(39)
+ CHAR(39) + N')''
);
';

EXEC sp_executesql @sql;

Answer 1

So OPENQUERY() is basically saying "Open this query on the linked server" - It's the preferred method for doing a select when able for me. Instead of saying something like

SELECT col1, col2
FROM LinkedServerName.DatabaseName.Schema.Table
WHERE SomeValue = Something Local Here

it does the filtering and joining at the linked server. This avoids you having to bring data back to just filter it out, etc with the four part query aproach.

The problem, though, is that update is trying to run on the destination. It's basically a pass-through query, so you are trying to pass that update through.

So you'll need to do the update locally, but can join to the open query to join to the data on the other side. For more you can see the syntax guide here with some notes.