Insert into TableName
SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');
If Table not exists
SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber into #XLImport1 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');
Tested:
Exec sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO
SELECT Sno,Data,Row_Number() Over (Order by Sno) as AutoNumber into #XLImport1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\test.xlsx','SELECT * FROM [Sheet1$]');
Select * FROM #XLImport1
Drop Table #XLImport1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0
GO
Exec sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO
Exec sp_configure 'show advanced options', 0
RECONFIGURE;
GO
Using OpenDataSource
SELECT * INTO #XLImport3
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\test.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
Check this
CleanCode Import to SqlServer from Excel