Loading multiple files into tables with TSQL

With the help of this code we can load multiple files into SQL Server table without using SSIS.

I tested this code by creating two excel files i.e. ProductCategory.xlsx & ProductCategory1.xlsx at path D:\Project\Files\ and loaded into the table dbo.ProductCategory.


IF OBJECT_ID('tempdb..#tempOpex') IS NOT NULL DROP TABLE #tempOpex
Declare @paramFilePath varchar(255)='D:\Project\Files\',@Sql varchar(max),@month int,@budVer varchar(3),@id int,
@fileToSelect varchar(3),@fileExists INT,@FileContents  VARCHAR(100),@paramFileName varchar(255),@paramfullpath varchar(500);

DECLARE @Opex TABLE
(
 Name varchar(255),
 Depth INT,IsFile INT
)

INSERT @Opex (Name,depth,IsFile)
EXEC master.sys.xp_dirtree @paramFilePath,1,1;


select Id=row_number() over(order by Name),* into #tempOpex from  @Opex

Select @id=max(Id) from #tempOpex

While (@id>=1)
Begin

Select @paramFileName=Name from #tempOpex where Id=@id
Set @paramfullpath=@paramFilePath+@paramFileName

EXEC master.dbo.xp_fileexist @paramfullpath, @fileExists OUTPUT

IF (@fileExists=1)
Begin

SET @Sql = 'Select * from OPENROWSET(''Microsoft.ACE.OLEDB.15.0'',
''Excel 12.0; HDR=YES;IMEX=1;Database='+@paramfullpath+''',''Select * from [ProductCategory$]'')'
INSERT INTO dbo.ProductCategory
EXEC (@Sql)
Set @id=@id-1
End
Else
Begin

Print 'File Does not exists'
End
End

If code gives following error

"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online."

then run the code below

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO