Transfer data from Excel to SQL Server

Question

I have an Excel spreadsheet that contains all my data that I need to put in a SQL Server database. I am pretty new o ASP.NET and have never exported from Excel to SQL Server before.

My Excel spreadsheets look like this:

Trade Header → ArtID → BusinessName → AdStyleCode → Address → Suburb

In SQL Server, I created a table called "Listings" that is in this format

intListingID → intCategoryID → BusinessName - ArtID → intAdCode → Address → Suburb

What would be the best way to export data from Excel and then import it into SQL Server 2005.

Thanks...

Answer 1

You can do this easily using SSIS, you can refer to these two links for complete information.

• Link 1
• Link 2

[EDIT]

If you have Express, you can try the following commands to set up a linked server and receive data

EXEC sp_addlinkedserver ExcelData,'Jet 4.0','Microsoft.Jet.OLEDB.4.0','C:\MyData.xls', NULL, 'Excel 5.0;' GO 

Then you can select data in your tables

 INSERT INTO Listings ... SELECT column1 AS intListingID, <put all columns here> FROM ExcelData...Data GO 

For other options check this link.