Insert data into a table from Open Query with variable

I am trying to using OPENQUERY to pull some data into a table. Here’s what my code looks like:

  DECLARE @TSQL VARCHAR(MAX)
  DECLARE @CD   VARCHAR(10) = 'XX'

  DECLARE @OracleData TABLE  (Cd VARCHAR(20), ApptDATE Datetime )
  
  INSERT INTO @OracleData(Cd,ApptDATE )

  SELECT  @TSQL = 'SELECT * FROM OPENQUERY(LinkedServer,''Select p.Cd, p.AppDate
                                                        from ta.table1 p 
                                                        where p.IdCode = ''''' + @CD + ''''''')'

  EXEC (@TSQL)

I end up with the following error:

An INSERT statement cannot contain a SELECT statement that assigns values to a variable.

When I attempt to run the EXEC(@TSQL) without the INSERT it works like a charm, but I am unable to do an insert.

Any ideas how I can possibly resolve this? Thanks.

Answer

You are doing this the wrong way round.

Don’t insert the @TSQL variable into your table, set the variable, then insert the results using INSERT...EXEC...

DECLARE @TSQL nvarchar(max) = '
SELECT *
FROM OPENQUERY(LinkedServer,
  ''Select p.Cd, p.AppDate
    from ta.table1 p 
    where p.IdCode = ''''' + @CD + ''''''')
';

INSERT INTO @OracleData (Cd, ApptDATE)
EXEC (@TSQL);

I’m sure there is an excellent reason you are not just using a straight Linked Server query without dynamic SQL, but I can’t think of one.