Insert Select statement

• clucasi

  SSCrazy

  Points: 2113

  More actions

  May 29, 2014 at 9:36 am

  #73571

  Hi all,

  Just wondering if anybody could help me.

  The statement below keeps throwing up the following error incorrect syntax near '@temp'

  the code is as follows

  declare @number as int

  declare @temp as varchar(50)

  set @temp= 'C:\' + cast(@number as varchar(50)) + '.xml'

  INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)

  SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()

  FROM OPENROWSET(BULK @temp, SINGLE_BLOB) AS x;

  When hard code the path in it works.

  Thanks

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 29, 2014 at 9:48 am

  #1717414

  Perhaps a variable in that part of the OPENROWSET clause isn't supported, although I can't find that documented anywhere. You may have to use dynamic SQL instead. Beware of SQL injection if you do.

  John

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  May 29, 2014 at 2:47 pm

  #1717598

  To the best of my knowledge, nothing in OPENROWSET can be a variable. It either needs to be hard-coded or be dynamic SQL. As previously suggested, be careful to ensure that your dynamic SQL is bullet proof when it comes to SQL Injection.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)