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

Question

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!

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