SQL Excel中生成SQL语句的技巧
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/315504/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Tricks for generating SQL statements in Excel
提问by David Vidmar
Do you have any tricks for generating SQL statements, mainly INSERTs, in Excel for various data import scenarios?
在Excel中针对各种数据导入场景生成SQL语句，主要是INSERT，有什么技巧吗？
I'm really getting tired of writing formulas with like
我真的厌倦了像这样写公式
="INSERT INTO Table (ID, Name) VALUES (" & C2 & ", '" & D2 & "')"
="INSERT INTO Table (ID, Name) VALUES (" & C2 & ", '" & D2 & "')"
回答by Jason V
The semi-colon needs to be inside the last double quote with a closing paren. When adding single quotes around a string, remember to add them outside your selected cell.
分号需要在最后一个双引号内，并带有一个结束括号。在字符串周围添加单引号时，请记住将它们添加到所选单元格之外。
(spaces added for visibility - remove before inserting)
（为可见性添加了空格 - 插入前删除）
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")
Here is another view:
这是另一种观点：
=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")
=CONCATENATE("insert into table (id, date, price) values (",C3,",'",D3,"',",B3,");")
回答by SamH
I used to use String concatenation method to create SQL inserts in Excel. It can work well but can also be a little time consuming and 'fiddly'.
我曾经使用字符串连接方法在 Excel 中创建 SQL 插入。它可以很好地工作，但也可能有点耗时和“繁琐”。
I created an Excel Add-In that makes generating Inserts from Excel easier :
我创建了一个 Excel 插件，使从 Excel 生成插入更容易：
(see the video at the bottom of the page) http://www.howinexcel.com/2009/06/generating-sql-insert-statements-in-excel.html
（见页面底部视频） http://www.howinexcel.com/2009/06/generating-sql-insert-statements-in-excel.html
http://www.querycell.com/SQLGenerator.html
http://www.querycell.com/SQLGenerator.html
回答by Shannon Severance
Sometimes I use substitute to replace patterns in the SQL command instead of trying to build the sql command out of concatenation. Say the data is in Columns A & B. Insert a top row. In cell C1 place the SQL command using pattern:
有时我使用替换来替换 SQL 命令中的模式，而不是尝试通过连接来构建 sql 命令。假设数据在 A 列和 B 列中。插入顶行。在单元格 C1 中使用模式放置 SQL 命令：
insert into table t1 values('<<A>>', '<<B>>')
Then in rows 2 place the excel formula:
然后在第 2 行放置 excel 公式：
=SUBSTITUTE(SUBSTITUTE($C, "<<A>>", A2), "<<B>>", B2)
Note the use of absolute cell addressing $C$1to get the pattern. Especially nice when working with char or varchar and having to mix the single and double quotes in the concatenation. Compare to:
请注意使用绝对单元寻址$C$1来获取模式。在使用 char 或 varchar 并且必须在串联中混合单引号和双引号时特别好。相比于：
=concatenate("insert into table t1 values '", A2, "', '", B2, "')"
An other thing that has bitten me more than once is trying to use excel to process some chars or varchars that are numeric, except they have leading zeros such as 007. Excel will convert to the number 7.
另一件事不止一次让我感到困惑，那就是尝试使用 excel 来处理一些数字字符或 varchars，除了它们有前导零，例如 007。Excel 将转换为数字 7。
回答by Peter Carswell
the VBA approach would be: declare your string and assign your SQL statement like this
VBA 方法是：声明您的字符串并像这样分配您的 SQL 语句
dim SqlString as String
SqlString = "SELECT * FROM %1 WHERE (var=%2)"
SqlString = Replace("%1", "Table_Name")
SqlString = Replace("%2", ""value"")
Excel approach is similar, but using the SUBSTITUTEfunction.
Excel 的做法是类似的，只是使用了SUBSTITUTE函数。
I prefer this approach because it makes the SQL text legible and avoids all the annoying & and concatenate problems. Yes, it takes an extra cell, but its worth it for the audit trail.
我更喜欢这种方法，因为它使 SQL 文本清晰易读，并避免了所有烦人的 & 和连接问题。是的，它需要一个额外的单元格，但它对于审计跟踪来说是值得的。
回答by RubberDuck
I know this pain. I ended up writing about it on my blog...twice.
I created a UDF that concatenates a range of cells together with several options. This will always comma separate the values, but will also optionally add single quotes and/or parenthesis as needed.
我知道这种痛苦。我最终在我的博客上写了它......两次。
我创建了一个 UDF，它将一系列单元格与多个选项连接在一起。这将始终以逗号分隔值，但也可以根据需要选择添加单引号和/或括号。
So, you write the easy part of the sql statement.
因此，您编写了 sql 语句的简单部分。
INSERT INTO table
VALUES /*string that we don't want to type by hand*/
or
或者
SELECT *
FROM table
WHERE foo IN (/*another string I don't want to type out*/)
And the custom excel function below will turn values in a spreadsheet range into a nice string for you.
下面的自定义 excel 函数会将电子表格范围内的值转换为适合您的字符串。
Function SQLConcat(rng As Range, Optional quoted As Boolean = False, Optional parenthesis As Boolean = False) As String
' ***************************************************************
' * Returns a comma separated list for use in SQL IN statements *
' * Params *
' * - rng: Range of cells to concatenate *
' * - quoted: True/False. If true, values are placed inside *
' * of single quotes. Default of false *
' * - parenthesis: Boolean. *
' * Useful for INSERT INTO tbl VALUES(53),(90),(397) *
' * *
' * Author: Christopher J. McClellan *
' * Published under Creative Commons Attribution-Share Alike *
' * http://creativecommons.org/licenses/by-sa/3.0/ *
' * You are free to change, distribute, and pretty much do *
' * whatever you like with the code, but you must give credit *
' * to the original author and publish any derivitive of this *
' * code under the same license. *
' ***************************************************************
Dim tmp As String 'temporary string
Dim row As Long 'first cell is special case
row = 0 'initalize row count
Dim c As Object 'cell
Dim txtwrapperLeft As String, txtwrapperRight As String
If quoted = True And parenthesis = False Then
txtwrapperLeft = "'"
txtwrapperRight = "'"
ElseIf quoted = True And parenthesis = True Then
txtwrapperLeft = "('"
txtwrapperRight = "')"
ElseIf quoted = False And parenthesis = True Then
txtwrapperLeft = "("
txtwrapperRight = ")"
Else
'quoted = false and parenthesis = false
txtwrapperLeft = ""
txtwrapperRight = ""
End If
For Each c In rng.Cells
If row = 0 Then
tmp = txtwrapperLeft & c.Value & txtwrapperRight
Else
tmp = tmp & "," & txtwrapperLeft & c.Value & txtwrapperRight
End If
row = row + 1
Debug.Print tmp
Next c
'return
SQLConcat = tmp
End Function
回答by Vivek Gajbhiye
For Inserting data into sql table
用于向sql表中插入数据
=CONCATENATE("INSERT INTO `database_name`.`table_name`(`Column_Name`,`Column_Name`) VALUES ( '",A1,"',",B1,"); ")
回答by WW.
I was doing this yesterday, and yes, it's annoying to get the quotes right. One thing I did was have a named cell that just contained a single quote. Type into A1 ="'"(equals, double quote, single quote, double quote) and then name this cell "QUOTE" by typing that in the box on the left of the lowest toolbar.
我昨天正在这样做，是的，正确引用引号很烦人。我做的一件事是有一个只包含单引号的命名单元格。键入 A1 ="'"（等号、双引号、单引号、双引号），然后通过在最下方工具栏左侧的框中键入该单元格将该单元格命名为“QUOTE”。
回答by Thorsten
Sometimes, building SQL Inserts this seems to be the easiest way. But you get tired of it fast, and I don't think there are "smart" ways to do it (other than maybe macros/VBA programming).
有时，构建 SQL 插入这似乎是最简单的方法。但是你很快就会厌倦它，我认为没有“聪明”的方法来做到这一点（除了宏/VBA 编程）。
I'd point you to avoid Excel and explore some other ideas:
我建议您避免使用 Excel 并探索其他一些想法：
- use Access (great csv import filter, then link to the DB Table and let Access handle the insert)
- use TOAD (even better import feature as it allows you to mix and match columns, and even to import from the clipboard)
- use SQL Loader (a bit tricky to use, but fast and pretty flexible).
- 使用 Access（很棒的 csv 导入过滤器，然后链接到数据库表并让 Access 处理插入）
- 使用 TOAD（更好的导入功能，因为它允许您混合和匹配列，甚至从剪贴板导入）
- 使用 SQL Loader（使用起来有点棘手，但快速且非常灵活）。
回答by DBMarcos99
Exporting an excel file as csv can be an alternative option (see Bill Krawin's post - as a new poster, I can't add comment yet). However be warned that you will probably have to change the formatting of your date fields to yyyy-mm-dd, otherwise the date columns will all show 00/00/00 – this is because MySQL uses a different date fomat to Microsoft Excel. Alternatively use OpenOffice to save the csv file.
将 excel 文件导出为 csv 可以是一个替代选项（请参阅 Bill Krawin 的帖子 - 作为新海报，我还不能添加评论）。但是请注意，您可能必须将日期字段的格式更改为 yyyy-mm-dd，否则日期列将全部显示为 00/00/00 – 这是因为 MySQL 使用与 Microsoft Excel 不同的日期格式。或者使用 OpenOffice 保存 csv 文件。
回答by onedaywhen
How about querying and inserting the data from the Excel workbook to the source using ACE/Jet (a.k.a. Access) SQL? This requires an ACE/Jet which could be another Excel spreadsheet. Here's a quick example:
如何使用 ACE/Jet（又名 Access）SQL 查询 Excel 工作簿中的数据并将其插入到源中？这需要一个 ACE/Jet，它可以是另一个 Excel 电子表格。这是一个快速示例：
INSERT INTO
[ODBC;Driver={SQL Server};SERVER=MYSERVER;DATABASE=MyDatabase;UID=sa;Pwd=mypassword;].MyTable (ID, Name)
SELECT F1, F2
FROM
[Excel 8.0;HDR=NO;IMEX=1;Database=C:\db.xls;].[Sheet1$A1:B4];
