将 Excel 电子表格数据导入现有的 sql 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20085249/
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
Import Excel Spreadsheet Data to an EXISTING sql table?
提问by Saudate
I have a table called tblAccounts whose contents will come from an excel spreadsheet.
我有一个名为 tblAccounts 的表，其内容将来自一个 Excel 电子表格。
I am using MS SQL Server 2008 (x64) on a Windows 8.1 (x64)
我在 Windows 8.1 (x64) 上使用 MS SQL Server 2008 (x64)
I tried using the SQL Server Import/Export Wizard but there is no option to choose an existing table but only an option to create a new one.
我尝试使用 SQL Server 导入/导出向导，但没有选择现有表的选项，而只有创建新表的选项。
I tried using other methods such as OPENROWSETS
我尝试使用其他方法，例如 OPENROWSETS
INSERT INTO tblAccount SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 12.0;Database=D:\exceloutp.xls','SELECT * FROM [Sheet1$]')
but gave me an error:
但给了我一个错误：
Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
消息 7308，级别 16，状态 1，第 1 行 OLE DB 提供程序“Microsoft.Jet.OLEDB.4.0”不能用于分布式查询，因为该提供程序配置为在单线程单元模式下运行。
Some research told me that it occurred because of a 64-bit instance of SQL server.
一些研究告诉我，它的发生是因为 SQL 服务器的 64 位实例。
The problem is that this Excel data transfer to a SQL table must be accomplished using the SQL Import/Export Wizard only.
问题是必须仅使用 SQL 导入/导出向导才能将 Excel 数据传输到 SQL 表。
How can I import an Excel spreadsheet to an existing SQL table without creating a new one?
如何在不创建新表格的情况下将 Excel 电子表格导入现有 SQL 表格？
Some links I visited but was not able to help me resolve my problem:
我访问过的一些链接无法帮助我解决问题：
采纳答案by Ron
Saudate, I ran across this looking for a different problem. You most definitely can use the Sql Server Import wizard to import data into a new table. Of course, you do not wish to leave that table in the database, so my suggesting is that you import into a new table, then script the data in query manager to insert into the existing table. You can add a line to drop the temp table created by the import wizard as the last step upon successful completion of the script.
Saudate，我遇到了这个寻找不同的问题。您绝对可以使用 Sql Server 导入向导将数据导入到新表中。当然，您不希望将该表留在数据库中，所以我的建议是您导入到一个新表中，然后在查询管理器中编写脚本以插入到现有表中。您可以添加一行来删除由导入向导创建的临时表，作为脚本成功完成后的最后一步。
I believe your original issue is in fact related to Sql Server 64 bit and is due to your having a 32 bit Excel and these drivers don't play well together. I did run into a very similar issue when first using 64 bit excel.
我相信您的原始问题实际上与 Sql Server 64 位有关，并且是由于您拥有 32 位 Excel 并且这些驱动程序不能很好地协同工作。我第一次使用 64 位 excel 时确实遇到了一个非常相似的问题。
回答by yash vora
You can copy-paste data from en excel-sheet to an SQL-table by doing so:
您可以通过执行以下操作将数据从 en excel-sheet 复制粘贴到 SQL 表：
- Select the data in Excel and press Ctrl + C
- In SQL Server Management Studio right click the table and choose Edit Top 200 Rows
- Scroll to the bottom and select the entire empty row by clicking on the row header
- Paste the data by pressing Ctrl + V
- 在 Excel 中选择数据并按 Ctrl + C
- 在 SQL Server Management Studio 中右键单击表并选择编辑前 200 行
- 滚动到底部并通过单击行标题选择整个空行
- 按 Ctrl + V 粘贴数据
Note: Often tables have a first column which is an ID-column with an auto generated/incremented ID. When you paste your data it will start inserting the leftmost selected column in Excel into the leftmost column in SSMS thus inserting data into the ID-column. To avoid that keep an empty column at the leftmost part of your selection in order to skip that column in SSMS. That will result in SSMS inserting the default data which is the auto generated ID.
注意：通常表有第一列，它是一个 ID 列，带有自动生成/递增的 ID。当您粘贴数据时，它会开始将 Excel 中最左侧的选定列插入到 SSMS 中最左侧的列中，从而将数据插入到 ID 列中。为避免在选择的最左侧保留一个空列，以便在 SSMS 中跳过该列。这将导致 SSMS 插入默认数据，即自动生成的 ID。
Furthermore you can skip other columns by having empty columns at the same ordinal positions in the Excel sheet selection as those columns to be skipped. That will make SSMS insert the default value (or NULL where no default value is specified).
此外，您可以通过在 Excel 工作表选择中与要跳过的列相同的顺序位置放置空列来跳过其他列。这将使 SSMS 插入默认值（或在未指定默认值的情况下为 NULL）。
回答by Hamid Reza
You can use import data with wizard and there you can choose destination table.
您可以使用向导导入数据，然后您可以选择目标表。
Run the wizard. In selecting source tables and views window you see two parts. Source and Destination.
运行向导。在选择源表和视图窗口中，您会看到两个部分。来源和目的地。
Click on the field under Destination part to open the drop down and select you destination table and edit its mappings if needed.
单击目标部分下的字段以打开下拉列表并选择目标表并根据需要编辑其映射。
EDIT
编辑
Merely typing the name of the table does not work. It appears that the name of the table must include the schema (dbo) and possibly brackets. Note the dropdown on the right hand side of the text field.
仅仅输入表名是行不通的。表名似乎必须包括模式 ( dbo) 和可能的括号。请注意文本字段右侧的下拉列表。
回答by Richard Briggs
If you would like a software tool to do this, you might like to check out this step-by-step guide:
如果您想要一个软件工具来执行此操作，您可能需要查看此分步指南：
"How to Validate and Import Excel spreadsheet to SQL Server database"
“如何验证 Excel 电子表格并将其导入 SQL Server 数据库”
