Import Excel Spreadsheet Data to an EXISTING sql table?
excel, import, sql, sql-server, sql-server-2008
Solution
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.
Problem
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 64-bit instance of SQL server. The problem is that this Excel data transfer to a 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? 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