How to import data from Excel to SQL Server?

We can import data from Excel to SQL Server by using SQL Server distributed queries.

We can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples will help us to import data from excel customer’s worksheet into new SQL Server tables.

SELECT * INTO tblXLImport1 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO tblXLImport2 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', [Customers$])

SELECT * INTO tblXLImport3 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test.xls', 'SELECT * FROM [Customers$]')

//Here Customers$ means the name of worksheet.

Simple sample query:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\students.xls;Extended Properties=Excel 8.0')...[Students$]

...S.VinothkumaR.