Openrowset Bulk Insert

• bunmiog

  SSC-Addicted

  Points: 448

  More actions

  March 7, 2008 at 10:49 am

  #187563

  I am trying to do this

  Declare @filename varchar(125)

  set @filename = 'C:\testdata.xml'

  select Bulkcolumn FROM OPENROWSET (BULK '"' +@filename +'"', SINGLE_CLOB) AS xmlDatatest

  I get this error: Incorrect syntax near '+'.

  When I do the same thing using a select statement

  SELECT '"'+ @filename +'"' AS testdata it works fine

  result is 'C:\testdata.xml'

  Any other way to do this.

  Thanks

  Bunmi

• Adam Haines

  SSC-Insane

  Points: 23217

  More actions

  March 7, 2008 at 11:35 am

  #786839

  You have to use dynamic sql:

  Declare @filename varchar(125)

  set @filename = 'C:\testdata.xml'

  DECLARE @sql NVARCHAR(500)

  SET @sql = N'

  select Bulkcolumn

  FROM OPENROWSET

  (BULK ''' + @filename + ''', SINGLE_CLOB) AS xmlDatatest

  '

  EXECUTE sp_executesql @sql

  ---

  
  My blog: http://jahaines.blogspot.com

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  March 7, 2008 at 1:15 pm

  #786882

  The reason why you have to do it with Dynamic SQL is because it will not allow you to do formulas in the query. The Query must be "hard -code" (for lack of a better term). The dynamic SQL makes it appear as such.

  --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)