SQL Server Read Excel Sheet

Today we had a requirement to insert data into database from excel sheet. I used POI Utility to read data from excel sheet and it works fine but problem occurs if the recordset has around thousands of record and request time out error occurs. So I was googling for a solution and came across sql server’s in-built function ‘OPENROWSET‘.Using this function we can read and write excel sheet from sql server.

Follow the procedure below to read or write excel’s data using this functionality;
To import Excel file, we need to do following:
1. Put Excel file on server, means we need to put files on server,
if we are accessing it from local.
2. Write following TSQL, to read data from excel file.

[code:sql]
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\test.xls’,
‘SELECT *
FROM [sheet1$]’)
[/code]
OR Write data from excel sheet.
[code:sql]
SELECT * INTO Temp
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:\Test.xls’,
‘SELECT *
FROM [sheet1$]’) [/code]