SP for Importing Excel into SQL Table..By using Dynamic query with parameter
Parameter is an ExcelName.
--Exec SP_Excel 'Report.XLSb'
Alter PROC SP_Excel
(@ExcelName VARCHAR(100)
)
As
Begin
Declare @SQL VARCHAR(1000)
if OBJECT_ID('Table1') is not null
drop table Table1
SET @SQL= 'select * INTO Table1 from openrowset (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=d:\ExcelFile\'+@ExcelName+''',[Sheet1$])'
EXEC(@SQL)
End