0

I just tried to add data from Excel file to SQL Server. I can import as a whole new table, but that means I have to assign the indexes again. Is it possible to import into an existing table?

I saw another question similar to this, and I tried what was suggested there, clicking 'write a query to specify the data transfer'

From this diagram

I entered the following query

INSERT INTO Customer (Customer_Id, Customer_Name, Customer_Company, Address, Phone)
   SELECT 
      A.[Column1], A.[Column2], A.[Column3], A.[Column4], A.[Column5]
  FROM 
      OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Excel.xls;HDR=YES', 'select * from [Sheet1$]') AS A;

I don't really understand the syntax of the second part of it.. and I'm getting a syntax error in the FROM clause.

Any clues?

Community
  • 1
  • 1
Tsar
  • 170
  • 1
  • 6
  • 20
  • 1
    Have you considered using SSIS? It's pretty easy to import an Excel Spreadsheet this way and probably more elegant than writing a SQL Query. – Greg the Incredulous Nov 06 '14 at 01:41
  • @greg Never really thought of using that before. Since i couldnt find a way i created the whole database again. :/ – Tsar Nov 06 '14 at 02:13

2 Answers2

2

The simplest way to import data from Excel for a one off import is to use the SQL Server Import and Export Wizard as shown in the picture in the question. You should choose Microsoft Excel as the data source, specify the file path to your spreadsheet, and specify whether the first row contains column headers. Then specify the target SQL server and database.

Choose the other option from the one selected in your diagram - "Copy data from one or more tables or views". You can then select the worksheet within your spreadsheet that you want to import, and specify the target table you want to put the data into. Importantly you can edit the mappings so that the data from your spreadsheet goes into the correct columns in your table - either an existing table or a new table. Behind the scenes the wizard is generating a similar script to the one in your question but you should not have to worry about it if you set up the wizard correctly.

From memory you can also convert this into a SSIS package so it is a repeatable process.

Greg the Incredulous
  • 1,676
  • 4
  • 29
  • 42
  • As i mentioned in the question, the question was for whether or not i can import an excel file into an _existing_ table or not.. Right now i have selected the "Copy data from one or more tables or views", and this meant that i had to import the data into new tables, which i later had to edit and insert foreign keys and other indexes. The question was about the 'write a query to specify the data transfer'. I found online tutorials on the previous ones, but not the one im looking for... – Tsar Nov 06 '14 at 05:08
  • Yes, you can definitely import into an existing table using this method. The things you need to consider are: 1. Ensuring that your data types match from spreadsheet to table 2. Considering the primary key that exists in the table. – Greg the Incredulous Nov 06 '14 at 05:19
  • I made sure that the data name (excel file, upper row and the name of columns in the db) matched. But the datatype might not have been right. I had values like "1001" inserted into the primary key. DB picked it up as float (when i painstakingly did it all over again), where as i had declared it on the table as a varchar. Might have been the issue, eh? But that doesnt explain the syntax error i got (in the question).. – Tsar Nov 06 '14 at 05:25
  • I would stick to using the wizard rather than using the SQL Syntax if you aren't sure - I just parsed your SQL statement and it doesn't throw any errors so I don't know without more information. In any case if you get the data types right then my answer should work for you. – Greg the Incredulous Nov 06 '14 at 05:30
  • I just noticed your comment about the primary key - that's likely to be the source of your problem. I suspect that the primary key in the database is an identity and you're trying to insert values into it. You don't need to if it's an identity column (primary keys often are) so you can let SQL take care of it for you. – Greg the Incredulous Nov 06 '14 at 05:38
  • So next time, SSIS is the way to go eh? Thanks anyway :) – Tsar Nov 06 '14 at 05:49
2

You can just copy and paste excel from cells in sql server. :DD

I'm seeing this right now, when I'm facing same problem as you.

This is what I did:

I copy my data from excel: enter image description here

Next in the sql server, I order my table to generate script for edit 200 rows:

enter image description here

And then in the last line on edit rows, you shoul past the values:

enter image description here

And... That's it! Easy peasy

Fábio Carvalho
  • 93
  • 15