I wouldn't discard anything from mr. gbn,Sankar Reddy,SQLChicken
. But i would like to say that there is simple way to import excel sheet in SQL is through 'OPENROWSET' TSQL.
For Example to import user
excel sheet in TestDB
Database. Suppose that i have already created the [user]
table in TestDB
Database. Then the simple
openrowset TSQL would be below mention like that
Use TestDB;
Go
select * INTO [User]
from openrowset('MICROSOFT.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\TestFolder\User.xlsx; HDR=yes; IMEX=1',
'select * from [User$]');
Go
Note : I am writing this TSQL in SQL Server 2012 environment. For Every SQL server openrowset OLEDB
provider will be different. Please check the OLEDB
link server->provider supported for that version of SQL server.
In my case it is ACE.OLEDB.12.0
.
Before import make sure that Allow inprocess
is checked or not of that MICROSOFT.ACE.OLEDB.12.0
provider. It should be checked like that

Note:- It's Tested in production environment. But in your case without to test in test environment. Don't directly run in Production Environment.