0

I am trying to update a table using openquery and a temp table. Whenever I execute the query I get the error The multi-part identifier "#tempTable.PERMITEE" could not be bound. The multi-part identifier "#tempTable.BILLTOPERSON" could not be bound.

I am not sure what I am doing wrong, everything seems to be correct. Help would be very much appreciated.

Thank you

Here is my query

BEGIN TRANSACTION
DROP TABLE #tempTable; 
SELECT [EN_ID], [PERMITEE], [BILLTOPERSON] INTO #tempTable FROM LakeEncroachments
SELECT * FROM #tempTable
UPDATE OPENQUERY([GIS-DB1], 'SELECT [EN_ID], [PERMITEE], [BILLTOPERSON] FROM [NaturalResources].[sde].[LAKEMANAGEMENT_LAKEENCROACHMENTS]')
SET [PERMITEE] = #tempTable.[PERMITEE], [BILLTOPERSON] = #tempTable.[BILLTOPERSON]
WHERE [EN_ID] IN (SELECT [EN_ID] FROM #tempTable)
kevorski
  • 816
  • 1
  • 11
  • 29

1 Answers1

3

The thing that is missing is the join between the OpenQuery and the temp table.

The update query should be something like:

UPDATE oq 
SET oq.[PERMITEE] = tt.[PERMITEE], 
    oq.[BILLTOPERSON] = tt.[BILLTOPERSON]
FROM OPENQUERY([GIS-DB1], 'SELECT [EN_ID], [PERMITEE], [BILLTOPERSON] FROM [NaturalResources].[sde].[LAKEMANAGEMENT_LAKEENCROACHMENTS]') AS oq
JOIN #tempTable AS tt ON oq.[EN_ID] = tt.[EN_ID]
promicro
  • 1,280
  • 7
  • 14