How to load multi dimensional data from Excel into relational database

I have an excel sheet that shows the height-weight acceptable by underwriting class (insurance). I would like to load it into a relational database table with the following columns - underwriting_class, height and weight. Is there a way to do the t开发者_如何学Goransformation thru' SQL

You can use SSIS (Integration Services) for this as either a one time load or save the package to load as often as your data updates in the spreadsheet.

You can manage the headers and many other column properties within the package. So, you would be able to create your new table(s) based on the pages in the workbook.

Here is a good primer on SSIS 2005

How to import an Excel file into SQL Server 2005 using Integration Services

Assuming underwriting_class is in column A, height is in B and weight is in C, create column D with a formula like this:

="INSERT INTO some_table(underwriting_class, height, weight) VALUES ('"&A:A&"', "&B:B&", "&C:C&",);"

• Fill that down for all the data you have
• Copy column D and paste into Notepad
• Save as a .sql file
• Create the table in your database
• Use whatever database client you use to load your .sql file into the database