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;