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

SELECT * INTO #tmp FROM Products
ALTER 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.