Declare @Provider nvarchar(100) Declare @ConnectionString nvarchar(1000) Declare @FileName nvarchar(1000) Declare @query nvarchar(4000) Declare @collMonth nvarchar(6) set @Provider= N'Microsoft.ACE.OLEDB.12.0' set @ConnectionString=N'Excel 12.0 Xml;HDR=YES;Database=' --set @FileName=N'E:\SharedE\chidTest\2011_NS.xlsx' --set @FileName=N'E:\SharedE\chidTest\2011_NS_66b86021-93a8-4a4b-8fc6-0bb3c3500cd0_5-21-2012.xlsx' set @FileName =N'E:\SharedE\chidTest\MisReportsTest\SysAdmin\MISCREPORTS\EXCELUPLOADS\2011_NS_66b86021-93a8-4a4b-8fc6-0bb3c3500cd0_5-21-2012.xlsx' set @query=N'Select * From [Sheet1$]' set @collMonth=N'201104' --SELECT * into #APColln FROM OPENROWSET(@FileName); declare @sql nvarchar(max) set @ConnectionString = @ConnectionString + @FileName SET @sql = 'SELECT * into #APColln FROM OPENROWSET(' + QUOTENAME(@Provider, '''') + ', ' + QUOTENAME(@ConnectionString, '''') + ', ' + QUOTENAME(@Query, '''') + ')' print @sql
SET @sql = 'INSERT INTO #APColln (Field1, Field2, Fieldn...) ' + 'SELECT * ' + 'FROM OPENROWSET(' + @Provider + ', ' + @ConnectionString + ', ' + @Query + ') AS DT' EXEC(@sql)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)