将Excel电子表格数据导入EXISTING sql表？ [英] Import Excel Spreadsheet Data to an EXISTING sql table?

问题描述

我有一个名为tblAccounts的表，其内容将来自excel电子表格。

我在Windows 8.1（x64）上使用MS SQL Server 2008（x64）

我尝试使用SQL Server导入/导出向导，但是没有选项可以选择一个现有的表，而只能选择一个新的。

我尝试使用其他方法，如OPENROWSETS

  INSERT INTO tblAccount SELECT * FROM OPENROWSET 'Microsoft.Jet.OLEDB.4.0'，
'Excel 12.0; Database = D：\exceloutp.xls'，'SELECT * FROM [Sheet1 $]'）
  

但给了我一个错误：

 消息7308 ，级别16，状态1，行1 
 OLE DB提供程序'Microsoft.Jet.OLEDB.4.0'不能用于分布式查询，因为提供程序配置为以单线程公寓模式运行。 
  

有研究告诉我，这是因为SQL Server的64位实例。

问题是，此Excel数据传输到SQL表必须仅使用SQL导入/导出向导完成。

如何将excel电子表格导入现有的SQL表，而不创建新表？

谢谢！

我访问过的一些链接，但无法帮助我解决我的问题：_
如何将excel电子表格导入SQL Server？
修复OLE DB错误

解决方案

横过这寻找一个不同的问题。您绝对可以使用Sql Server导入向导将数据导入到新表中。当然，您不希望将该表放在数据库中，所以我建议您导入到新表中，然后将查询管理器中的数据脚本化以插入到现有的表中。您可以添加一行以将导入向导创建的临时表作为脚本成功完成后的最后一步。

我相信您的原始问题实际上是相关的到Sql Server 64位，是因为你有一个32位的Excel，这些驱动程序不能很好地在一起玩。我第一次使用64位excel时遇到了一个非常类似的问题。

I have a table called tblAccounts whose contents will come from an excel spreadsheet.

I am using MS SQL Server 2008 (x64) on a Windows 8.1 (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.

I tried using other methods such as 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.

Some research told me that it occurred because of a 64bit instance of SQL server.

The problem is that this excel data transfer to an SQL table must be accomplished using the SQL Import/Export Wizard only.

How can I import an excel spreadsheet to an existing SQL table without creating a new one?

Thanks!

Some links I visited but was not able to help me resolve my problem:
How do I import an excel spreadsheet into SQL Server?
Fix OLE DB error

解决方案

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.

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.

这篇关于将Excel电子表格数据导入EXISTING sql表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！