I had to import data to a SQL table from excel sheet couple of days ago. As the data is huge I cannot manually insert so I have decided to use SQL Server Import/Export Wizard.
Started importing the spread sheet by click on Enable Identity Insert option in the wizard but getting errors. Here is the best way I found.
Create your original SQL table with identity column. Import the spread sheet in to the default table (Sheet1$) in the same database. You can use different server if you have linked servers between both of the servers.
I have imported the data in the default table of same DB and inserted in the main table using T-SQL.