SQL Server Bulk Insert into a Table with an Identity Column

This is something that I've seen far too much bad advice around on Google, therefore I thought I'd set the record straight with the best way of inserting data into a SQL Server table that has an Identity column using the BULK INSERT command.

This doesn't require you to have an Identity on the data being imported and it definitely doesn't require some nasty SQL to make it work (the most common sugestion I saw was to add and drop columns on your destination table).

Anyway, have a watch and let me know what you think in the comments below.

Check out our other videos as well, we cover basic PowerShell and Docker alongside our SQL Server based content and we're hoping to expand out further into Power BI and other areas.

[miloniko]

That is the perfect solution! As you said, some worked with views and not repeatable, but you did! Thanks for sharing, please keep on going with such solutions this really helps.

[leeofrivian]

Nice work Kutech! This walkthrough was excellently explained, I'll be checking your channel out for more tips and tricks for sure! Keep it up

[CZGrizzly]

Thank you very much, you solution was helpful.

[andycoffman]

Hey Kutech, have you ever written a bulk insert that preserves the identity column from the source table without having to alter/drop columns on the destination? Multiple column data types had to be changed on a 100GB table. Altering the table took forever and offered no roll back. I decided to create a table copy, but no bulk insert or tablock options worked to keep the identity column.