RE: Does/can OUTPUT clause turn identity_insert on & off

SSC Guru

Points: 186127

August 17, 2012 at 2:55 am

Phil Parkin (8/17/2012)
Missing something, I'm afraid.
SELECT ... INTO
always creates a new table. When it creates that table, uses the data types of the source columns, but not primary keys or identity definitions. So, in your example, Archive.MyTest.Id will not be an IDENTITY column, despite your comment to the contrary. It's just a bog-standard INT.

Sorry Phil but this is incorrect - here's the generated script for table Archive.MyTest:

CREATE TABLE [Archive].[MyTest](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Descript] [varchar](30) NULL

) ON [PRIMARY]

Archive.MyTest.Id is an IDENTITY column.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden