xxxxxxxxxx
From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data.
This opens the Import Data Wizard, which typically works pretty well for importing from Excel.
xxxxxxxxxx
# For file excel 2007 version (*.xlsx)
INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\test.xlsx', [Customer$])
# For file excel 97-2003 version (*.xls)
INSERT INTO MyTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\test.xls', [Customer$])
xxxxxxxxxx
Go to "Object Explorer", find the server database you want to export to Excel. Right-click on it and choose "Tasks" > "Export Data" to export table data in SQL. Then, the SQL Server Import and Export Wizard welcome window pop up.
xxxxxxxxxx
Step 1 – Create a Project.
Step 2 – Create a Connection to your SQL Server Database.
Step 3 – Create a Table.
Step 4 – Create an Excel Connection.
Step 5 – Create a Data Flow Task.
Step 6 – Creating the Excel Source.
Step 7 – Removing Rubbish Data.
Step 8 – Piping the 'OK Data' into a SQL Server Table.