所以我设法让它工作。这是使用Excel 2010，SQL Sever 2012.

这假设excel中的行标题与sql中的列标题相同。

这也假定表的类似的布局如在问题的帖子中。

---

要将Excel中的数据导入SQL服务器，使用UDF，我们可以执行以下操作。

在excel中创建一个公共函数

  public function import_data（TableID，vHeader As Variant，vRow As Variant，vData As Variant）
结束函数
 

并使用您喜欢的连接方法连接到数据库。

然后对于 INSERT 语句使用以下内容：

 '声明变量
 Dim vpush As String 
 Dim headerCount As Long 
 Dim rowTitles As String 
 Dim rowDatas As String 
 Dim i As Long 

`计算列数
 headerCount = Application.CountA（vHeader）

`创建插入统计ement 
对于i = 1 To headerCount 
 rowTitles = VBA.Join（WorksheetFunction.Transpose（vRow），，）
 rowDatas = VBA.Join（WorksheetFunction.Transpose（Application.Index（ vData，，i）），'，'）
 vpush =INSERT INTO example_table（id，& rowTitles& ，year）VALUES（'&，和rowDatas和，& vHeader（i）&'）
 Next i 
 

不要忘记 .Execute（vpush）。 / p>

比起任何时候你想导入一个表，你将在excel工作表中执行以下操作：

1. 在单元格类型 = import_data（并按 CTRL + SHIFT + A


2. 选择TableId


3. 选择标题列（选择列为所有行标题（这将是您的sql中的标题）


4. 选择所有数据，表

应该这样做。

（PS如果这不是最有效的方式，或者你看到改进...随意编辑或添加）

I am trying to import a data table from excel into SQL Server 2012, using VBA.
Preferably with help of a UDF.

The excel table looks something like this.

TableID    2012  2011  2010  2009
row 1       11     12   13    14
row 2       21     22   23    24
row 3       31     32   33    34
etc..

(I placed the numbers in the cells to designated their position. For example 11 = row 1, column 1)

The database table looks something like this.

Header:        id   |  year  |  row1  |  row2  |  row3  |  etc..
ExampData:  TableId    2012      11       21       31       ..
ExampData:  TableId    2011      12       22       32       ..

(This doesn't include the Primary Key column, which can be either a combination of id and year, or a NEWID())

---

I know how to import specific columns, for example I could run the following code:

INSERT INTO example_table (id, year, row1, row2, row3) VALUES ('001', '2012', '11', '21', '31')

This works great for individual columns, but how would I go about making it work for the entire table (multiple columns and rows).

So I managed to get it working. This is using Excel 2010, SQL Sever 2012.
This assumes the row titles in excel are the same as the column headings in the sql.
This also assumes a similair layout of tables as in the question post.

---

To import data from Excel into the SQL server, using UDF we can do the following.
Create a Public Function in excel:

Public Function import_data(TableID, vHeader As Variant, vRow As Variant, vData As Variant)
End Function

And use your favorite connection method to connect to the database.
Then for the INSERT statement use the following:

'Declare the  variables
Dim vpush As String
Dim headerCount As Long
Dim rowTitles As String
Dim rowDatas As String
Dim i As Long

`Count the amount of columns 
headerCount = Application.CountA(vHeader)

`Create insert statement
For i = 1 To headerCount
   rowTitles = VBA.Join(WorksheetFunction.Transpose(vRow), ", ")
   rowDatas = VBA.Join(WorksheetFunction.Transpose(Application.Index(vData, , i)), "', '")
   vpush = "INSERT INTO example_table (id, " & rowTitles & ", year) VALUES ('" & TableID & "', '" & rowDatas & "', '" & vHeader(i) & "')"
Next i

Don't forget to .Execute (vpush).

Than anytime you want to import a table, you will do the following in an excel worksheet:

1. In a cell type =import_data( and press CTRL + SHIFT + A
2. Select TableId
3. Select the heading row (assumed always to be years here)
4. Select column with all row titles (which will be the heading titles in your sql)
5. Select all data in table

That should do it.
(P.S. If this is not the most efficient way, or you see improvements.. feel free to edit or add)