VBA：将Excel表格导入SQL

Question

我将尝试使用VBA将Excel中的数据表导入SQL Server 2012。
最好使用UDF来辅助导入。

Excel表格看起来大致如下。

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

我把数字放在单元格中以指定它们的位置。例如11 = 第1行，第1列。

数据库表看起来像这样。

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

（这不包括主键列，主键列可以是id和year的组合，或者是NEWID()）

---

我知道如何导入特定的列，例如我可以运行以下代码：

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

这对于单独的列非常有效，但是如何使其适用于整个表格（多个列和行）。

- Ben Z.

---

这是一次性或不经常运行的操作吗？如果是，那么您不必过于担心效率。除非要导入大量行。您为什么要尝试提高效率呢？ - Nick DeVore

这将是定期的，每天会有多个表格。 - Ben Z.

Answer 1

所以我成功地使它工作了。这是使用Excel 2010、SQL Server 2012完成的。
这假设Excel中的行标题与SQL中的列标题相同。
这还假设表的布局与问题帖子中的类似。

---

为了将数据从Excel导入SQL服务器，我们可以使用UDF执行以下操作。
在Excel中创建一个公共函数：

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

使用您喜欢的连接方法连接到数据库。
然后，对于INSERT语句，请使用以下内容：

'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

不要忘记执行.Execute (vpush)。

然后，每当您想要导入一个表格时，在Excel工作表中进行以下操作：

1. 在单元格中输入=import_data(并按下CTRL + SHIFT + A 2. 选择TableId 3. 选择标题行（这里假定始终为年份） 4. 选择包含所有行标题的列（这将成为您SQL中的标题名称） 5. 选择表格中的所有数据

就这样，完成了。
（注：如果这不是最有效的方法，或者您看到了改进的地方，请随意编辑或添加）

- Ben Z.

Answer 2

如果您使用的是SQL Server 2008或更高版本，则可以在INSERT语句中插入多行：

INSERT INTO example_table (id, year, row1, row2, row3)
VALUES ('001', '2012', '11', '21', '31'),
   ('002', '2012', '12', '22', '32'),
   ('003', '2012', '13', '23', '33')

根据您的电子表格大小，您可以使用一个大的INSERT或几个较小的INSERT进行操作。

- user325117

---

谢谢！能否将“VALUES”行变成一个变量？我从来不确定会有多少行。类似于“INSERT INTO example_table (id, year, row1, row2, row3) VALUES Var(i)”这样的语句是否可行？然后使用“For i = 1 to NumRows”。 - Ben Z.