Importing Data to SQL the Hard Way

“Miracle worker” is not part of my job title, but there are times when what I am asked to do, coupled with the limitations placed on me, cause me to fall little short of that level.

在我的职业生涯中，有一次我被要求将许多电子表格中的数据导入SQL Server. 问题是DBA(数据库管理员)不会授予我执行批量插入所需的权限，因为我是一名顾问, and according to company policy, only full-time employees could have those rights.

所以我就这样，打开电子表格，不知道如何将数据放入数据库. 如果没有批量导入选项，那么放入数据的唯一方法就是通过T-SQL.

虽然Excel确实是一个电子表格，是为处理数字而制作的, 它还具有优秀的文本操作功能，这些功能经常被忽略. 我决定使用Excel中的数据函数来生成T-SQL，然后可以在SSMS (SQL Server Management Studio)中运行以插入数据.

Table Value Constructor

为了方便地将这些数据转换为T-SQL，我们将使用 table value constructor, also known as the VALUES keyword. 这允许我们将多行插入到表变量中，并从那里开始工作.

The basic syntax of this command is:

INSERT INTO <*tablename*> (*field list*) VALUES
(*scalar values*),…(*scalar values*);

Our Sample Spreadsheet

For this example, I have a three-column spreadsheet. 第一列(A)是一个包含实践数字的整数值. 第二列(B)是字符串格式的实践管理器. 第三列(C)是实践状态，也是字符串.

我们将使用下一列(D)创建必要的T-SQL，以便用电子表格中的信息创建和填充表变量. 一旦填充了列D，我们就可以直接复制粘贴到T-SQL中.

Setting Up the Table Variable

第一个任务是声明将保存信息的表变量.

In Cell D1, the text becomes:

DECLARE @TempTable(PracticeNumber INT PRIMARY KEY, PracticeManager VARCHAR(100), PracticeState VARCHAR(2)) 

DECLARE @TempTable(
    PracticeNumber INT PRIMARY KEY, 
    PracticeManager VARCHAR(100), 
    PracticeState VARCHAR(2)
)

表变量只有在变量声明期间创建时才能有索引, so I have created the primary key at this time.

Setting Up the Insert

批处理的第一行需要有INSERT语句. We put this as the start of cell D2:

INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES 

INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES

Setting Up the Values (Data)

The VALUES statement is easy to set up in each row. 我们将使用公式为每一行构造一个文本字符串.

="(" & TEXT(A3,"#") & ",'" & B3 & "','" & C3 & "'),” 

="(" & TEXT(A3,"#") & ",'" & B3 & "','" & C3 & "'),”

第一件事是非常重要的等号，它让Excel知道它必须做一些工作. Then we add the open parentheses to bracket this row.

Next, 该值在Excel中是一个整数，并将进入表变量的整数列, 因此，我们使用text函数将A列中的单元格转换为文本.

逗号紧随其后，表示我们移动到另一个值. 这是一个文本字符串，所以我们必须在前后加上单引号.

Column C is also text, 所以我们在两边加上引号，然后用右括号结束.

然后我们添加逗号，以便我们可以继续到下一个值.

This formula generates text that looks like this:

(628180,'Tony Stark','NJ'),

SQL Limitations

表值构造器一次只能导入1000条记录. This means that every 1,000 records, 我们需要用分号结束前一批，并以新的INSERT语句开始.

This requires a bit more text manipulation.

=IF(AND(A1<>"",A2=""),";",IF(MOD(ROW(A2),950) = 2, "INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES",",") &"(" & TEXT(A2,"#") & ",'" & B2 & "','" & C2 & "')")

=IF(AND(A1<>"",A2=""),";",IF(MOD(ROW(A2),950) = 2, "INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES",",") &"(" & TEXT(A2,"#") & ",'" & B2 & "','" & C2 & "')")

=IF(AND(A1<>,A2=_),";"...

公式的开始部分检查这个单元格中是否应该有任何内容. 如果当前行中没有数据，这个IF条件将为整个脚本放入终止分号, but there is data in the previous row.

,IF(MOD(ROW(A2),950) = 2, ";

ELSE的第一部分查看行号. 如果行号是2超过950，它将终止当前语句并开始下一个语句. 我使用950而不是1000，因为它在数学上有更多的回旋余地.

在第2行，第952行，以此类推，它将插入语句的开始:

INSERT INTO @TempTable(PracticeNumber, PracticeManager, PracticeState) VALUES"

在其他行中，它将在列表值之间添加必要的逗号.

Using the Formula

Once the formula is set, 你可以把公式复制到Excel中，只要你需要, plus one row to get the final termination.

电子表格输出如下所示(请注意, 我已经调整了公式，以每6行终止批处理，因此输出是清晰的):

Conclusion

在无法访问批量插入的情况下，Excel非常方便地生成SQL. By using the text and logic functions, 您可以生成SQL，以便轻松插入大量数据.