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.