Loading Multiple Excel files dynamically

• demin99

  SSC Eights!

  Points: 927

  More actions

  February 6, 2009 at 11:55 am

  #128660

  This is my code:

  Set @ID = '11'

  Set @Dest = C:\data.xls

  INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0; Database=@Destination ;',

  'Select tab1,tab2 FROM [Sheet1$]')

  select tab1,tab2 from dbo.mytable

  where ID = @ID

  )

  I need to pass this @Dest, @ID

  dynamically since I have multiple files to load.

  Iam getting error when I do this

  Is this Possible..?

  Thanks for your help...!!

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 6, 2009 at 3:11 pm

  #939826

  From BOL at

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f47eda43-33aa-454d-840a-bb15a031ca17.htm

  OPENROWSET does not accept variables for its arguments.

  You could use dynamic sql to perform the import something like:

  DECLARE @Sql NVARCHAR(500)

  Set @Sql = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0; Database=' + @Dest + ' ;',

  'Select tab1,tab2 FROM [Sheet1$]')

  'select tab1,tab2 from dbo.mytable

  where ID = @ID)'

  EXEC @Sql

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]