Import Excel Spreadsheet Data to an EXISTING sql table

Question

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

User · Answer

You can copy-paste data from en excel-sheet to an SQL-table by doing so:

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

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

User · Answer

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   http   leansoftware net forum en-us help excel-database-tasks worked-examples how-to-import-excel-spreadsheet-to-sql-server-data aspx

User · Answer

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

User · Answer

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

[sql] Import Excel Spreadsheet Data to an EXISTING sql table?

Examples related to sql

Examples related to sql-server

Examples related to excel

Examples related to sql-server-2008

Examples related to import