As digimanus said.. ODBC/OLEDB connection is the key.
Assuming, you have a WorkBook with three WorkSheets namely Sheet1, Sheet2 and Sheet3 with each sheet having data corresponding to one SQL table.
All you need to do is open an OLEDB connection on your workbook and read all the three sheets using this connection.
Assuming, you have a similar table structure in your database, you can drop this data into your database as it is.
Follow the steps below to achieve the above task:
1)
Open OLEDB connection on Excel: To do this, you will mostly spend time to work out a perfect connection string which actually works on your Excel version(
trust me it is painful sometimes!). check this
link[
^] to get some help with your connection string.
2)
Read Data from Excel: Create an OLEDB Command object and fetch data from excel sheet just like the way you fetch data from SQL database and populate your data sets.
SELECT * FROM [Sheet1$]
SELECT * FROM [Sheet2$]
SELECT * FROM [Sheet3$]
3)
Open a SQL connection: Using SqlConnection class with a specific connection string
4)
Insert Data in SQL: Insert the data fetched in data sets from Excel in step 2 into SQL using SQL insert statement.
5) Close all connections and you are done!
You will find a lot of examples on internet. Also, if it is a one time activity, you can use
SQL Server Import and Export Wizard[
^].
Submit an article or tip