There are many ways to load excel data into SQL Server database, using DTS, BULK import, SSIS and many others. In this post I will go with OPENROWSET. This is very useful when we need ad-hoc connection to an OLE DB source.
Here below is the image of the excel file taken for demonstrate.
Here is the script to import the file into SQL Server database using OPENROWSET.
SELECT exl.*
INTO #myExcelData
FROM OPENROWSET ('Microsoft.Ace.OLEDB.12.0'
,'Excel 12.0; Database=C:\Import\ExcelDataImport.xlsx; HDR=YES'
,'SELECT * FROM [Sheet1$]') AS exl
GO
Now see the data imported into our table.
SELECT * FROM #myExcelData GO
Here I have taken Excel 2007 as source and a SQL Server temp table as destination. Let me know if this is useful to you.
