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:
This question is related to
sql
sql-server
excel
sql-server-2008
import
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"
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.
You can copy-paste data from en excel-sheet to an SQL-table by doing so:
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.
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).
Source: Stackoverflow.com