How to import data from Excel to existing table in the bottom while handling auto Increment column in SSMS?

Question

I have an existing table like below in SSMS (SQL Server Management Studio).

Students_Table

UID  |  ID  |   Name  |  Location |
-----+------+---------+-----------+
1        1       Jogn      US
2        2       Alia      UK

where in UID is an auto increment and ID,Name,Location are my data columns

So I have an excel with data like below:

ID  |   Name  |  Location |
----+---------+-----------+
3       Jk        Santa
4       Lima      PS

Now the above two columns should be added to the bottom of the above existing Students_Table in SQL Server, but how to handle the auto increment column now?

Am I suppose to add the that column in my excel and import by considering the next incremented number?

Answer 1

To be able to copy and paste the data from Excel to SQL Server, you will have to include an empty column in Excel for the identity (UID) column:

You can check out my blog article that describes how to copy data from Excel to SQL Server for further details. Section 2b includes a step-by-step instruction with screenshots for your case when having an identity column.