INSERTING AN IDENTITY COLUMN IN THE SQL TABLE FROM EXCEL SPREAD SHEET USING SQL SERVER IMPORT AND EXPORT

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.