Insert data into a table from Open Query with variable

Question

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.

Print the @Tsql value with raiserror(), and see what you actually get there. You should also look into sp_executesql() to make this a little safer. What you have is ringing a few warning bells around a major potential security issue. Finally, the INSERT needs to be part of the sql string or this won't be able to work. — Joel Coehoorn
– Joel Coehoorn, Commented Feb 17, 2022 at 21:30

Charlieface · Accepted Answer · 2022-02-17 22:56:43Z

1

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.

answered Feb 17, 2022 at 22:56

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert data into a table from Open Query with variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related