However, when you need it, you need to do it quickly and efficiently.
At first, will create a table:
Here is how you would do this:
That is easy and fast, but what would you do if you need to insert more values than one?
Clicking on F5 again and again?
Approach #2That is quick and dirty way to insert 1000 records, but what if you need to insert million or more?
That "GO" will be too slow. So, approach #3 comes.
Approach #3Third approach is to load all values in bulk and I currently see two pretty easy ways of doing it:
A. Adding extra column, fill it in and then delete it.
I do not think it is the most effective way because requires extra space for a column and then for index rebuilt to gain that space back.
B. Allowing insertion into the Identity column
Both Inserts A and B have the very same result and produce the same size data, but surprisingly, even though Approach 3.A. looks uglier and requires Index Rebuild it works much faster than Approach 3.B. because of Window function "ROW_NUMBER" and associated with it spill in tempDB for Sorting operation.