编辑和校对-有什么区别?
April 30, 2024
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,以便轻松插入大量数据.