'How to create identity column when importing data from Excel into MS SQL Server (with Import and Export Wizard)?

I need to import great amount of data from excel into MS SQL Server, using the Import/ Export Wizard. Then I'll continue importing more data into the same table on a weekly basis.

The bad thing is that my excel data doesn't have an identity column, which to use as a primary key. The only option with what available is to use 2 string columns as a primary key, which is not a good idea.

Is there a way the sql server to add auto-identity column (integer) when importing the data, and what's the trick? I prefer such a column to be automatically added, because I'll need to import big amount of data into the same table on a weekly basis.

I tested a couple of times (with no success) and looked for a solution in the internet, but didn't found an answer to that particular question. Thanks in advance!

sql-serverdatabaseexcel


Solution 1:[1]

You can create the table first along with the new identity column. 

CREATE TABLE YourTable
(
  id INT IDENTITY,
  col1....
  col2....
  col3....
  PRIMARY KEY(id)
)

Then run the import/export wizard. When you get to the destination section pick your newly created table and map all the fields except the identity column. After the import you can check the table and see the id column has been populated.

Solution 2:[2]

Column names in Excel sheet should be same as that of sql Table. Map Excel columns with that of SQL table columns by Clicking Edit Mapping. Just don't map that (identity) column of sql table to anything . In Import-Export Wizard don't check Enable identity insert Checkbox. (Leave that un selected). and go ahead import .. This worked for me. !!!!! Previously when i used to check Enable identity insert it used to give me error.

Solution 3:[3]

I had a similar issue. I have a SQL table with an identity column (auto increment ID value) defined. I needed to import an Excel spreadsheet into this table. I finally got it to work via the following:

  1. Do NOT add a column to the Excel spreadsheet for your identity column in the SQL table.

  2. When you run the import wizard and get to the Edit Mappings step, do NOT select the Enable identity insert checkbox. This, in particular, was tripping me up.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Paresh Wadekar
Solution 3 Dave R

Related Questions