Appending an IDENTITY Column to a Temporary Table

Microsoft’s SQL Server development team provides code to illustrate the best way to append an IDENTITY column to a temporary table.

ITPro Today logo in a gray background | ITPro Today

I want to append an IDENTITY column to the temporary table that the following SELECT INTO statement returns:

SELECT * INTO #tmp FROM ProductsALTER TABLE ADD columnID INT IDENTITY

Although I currently use ALTER TABLE, I suspect I'm making the query more complicated than I need to. How can I append the IDENTITY column another way?

We don't recommend using ALTER TABLE to append an IDENTITY column because it logs an update on a row-by-row basis. And in the absence of the appropriate fill factor, ALTER TABLE also causes page splits. Using SELECT INTO with the IDENTITY(type, seed, increment) clause is a more efficient method because SQL Server has to pass through the data only once and SELECT INTO uses the FAST BULK LOAD APIs to copy the data. Listing 2 shows SELECT INTO used with the IDENTITY clause and a Bulk-Logged Recovery model to ensure minimal logging.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like