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:
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.