Why is IDENTITY_INSERT ON only allowed on one table at a time?
I think it's to make it difficult.  If you could just leave it on all the time, why even have an identity field?

There are actually a couple of restrictions, though:


It persists only on that connection
It's only settable on one table per connection


Based on the connection-related restrictions, I think it's mainly so it's never accidentally left ON.

Imagine if someone turned on ID insert on one of your tables, then you didn't realize and a (normally) invalid insert was performed that broke the integrity of your ID field?

Bear in mind ID fields can have duplicate values if there is no constraint or unique index in place...