SP For Importing Excel to SQL Table

Saranya Boopathi
Posted by Saranya Boopathi under Sql Server category on | Points: 40 | Views : 1395
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

Comments or Responses

Login to post response