OPENQUERY into temp table

Question

I'm trying to use OPENQUERY to get some data from MySQL, but I need to get the results into a temp table to manipulate.

I've tried a couple of examples but getting the same error:

DECLARE @lastid int = (SELECT MAX(remoteid) FROM cdr)
DECLARE @TSQL varchar(8000)
SELECT  @TSQL = 'SELECT * FROM OPENQUERY([PBXA_MYSQL],''SELECT * INTO #tempcdr FROM asterisk.cdr WHERE id > ''''' + CAST(@lastid as varchar(50)) + ''''''')'
EXEC (@TSQL)

SELECT * FROM #tempcdr

Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PBXA_MYSQL".

A normal select works fine, but select into has that error.

Thanks

score 0 · Answer 1 · answered Jun 01 '18 at 13:36

You're storing the results of the OPENQUERY into a temp table on the remote server, not the local server. Also, the temp table only exists in that session, as a local temp table, you'll need to make it global

To get the results, you'll need to change your query to be this:

DECLARE @lastid int = (SELECT MAX(remoteid) FROM cdr)
DECLARE @TSQL varchar(8000)
SELECT  @TSQL = 'SELECT * INTO ##tempcdr FROM OPENQUERY([PBXA_MYSQL],''SELECT * FROM asterisk.cdr WHERE id > ''''' + CAST(@lastid as varchar(50)) + ''''''')'
EXEC (@TSQL)

SELECT * FROM ##tempcdr

OPENQUERY into temp table

1 Answers1