Appending an IDENTITY Column to a Temporary Table

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.

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish