Does/can OUTPUT clause turn identity

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Does/can OUTPUT clause turn identity_insert...

Does/can OUTPUT clause turn identity_insert on & off

Rate Topic

Display Mode

Topic Options

Author

Message

dba-wannabe

Posted Thursday, August 16, 2012 1:33 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 12:06 PM
Points: 10, Visits: 145

Does/can the OUTPUT clause turn identity_insert on & off ‘behind the scenes’? I think it does, but I can't find that in BOL. Am I correct or missing something?
Thanks

Create Table dbo.MyTest (
Id int identity(1,1),
Descript varchar(30))

Insert dbo.MyTest (Descript) values (‘apple’)
Insert dbo.MyTest (Descript) values (‘banana’)
Insert dbo.MyTest (Descript) values (‘coffee’)
Insert dbo.MyTest(Descript) values (‘donut’)

SELECT TOP (0) * INTO Archive.MyTest FROM dbo.MyTest
--- the ‘id’ column in Archive.MyTest is an identity column.

DELETE dbo.myTable
OUTPUT deleted.id, deleted. Descript INTO Archive.MyTest(id, Descript)
WHERE id = 3
/*At this point, should the id value in the Archive table be 1 since that column in archive.MyTest is an identity or should it be the value that was deleted, 3?
It is a 3, which seems like identity_insert was set on & off. */

--Then, without explicitly listing the identity column:
DELETE dbo.myTable
OUTPUT deleted. Descript INTO Archive.MyTest
WHERE myTable.Descript = ‘banana’
--The Id of this row in Archive.MyTest is 4, clearly a new identity value.

Post #1346225

Phil Parkin

Posted Friday, August 17, 2012 1:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 4,324, Visits: 9,664

Missing something, I'm afraid.

SELECT ... INTO [table]

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.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1346360

Dave Ballantyne

Posted Friday, August 17, 2012 2:47 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943, Visits: 8,229

Hi, this is a bug

https://connect.microsoft.com/SQLServer/feedback/details/690839/outplut-clause-into-table-with-identity-ignores-set-identity-insert-setting

Clear Sky SQL
My Blog
Kent user group

Post #1346396

ChrisM@Work

Posted Friday, August 17, 2012 2:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

Phil Parkin (8/17/2012)

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1346399

Eugene Elutin

Posted Friday, August 17, 2012 3:27 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 2,596, Visits: 4,507

Dave Ballantyne (8/17/2012)

Hi, this is a bug

https://connect.microsoft.com/SQLServer/feedback/details/690839/outplut-clause-into-table-with-identity-ignores-set-identity-insert-setting

Actually, the BoL states, that you shouldn't include identity columns in the column list of output_table, so reported behaviour is the bug (or free feature, if you like Hehe

).

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1346420

Phil Parkin

Posted Friday, August 17, 2012 3:37 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 4,324, Visits: 9,664

ChrisM@Work (8/17/2012)

Phil Parkin (8/17/2012)

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.

That will teach me to not try and answer questions before drinking coffee, thanks Chris.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1346427

Eugene Elutin

Posted Friday, August 17, 2012 3:56 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 2,596, Visits: 4,507

...
That will teach me to not try and answer questions before drinking coffee, thanks Chris.

Seeing your avatar, I wouldn't think that you are great coffee-lover. Are you sure that you need that particular drink, especially on Friday? Hehe

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1346439

ChrisM@Work

Posted Friday, August 17, 2012 4:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

Eugene Elutin (8/17/2012)

...
That will teach me to not try and answer questions before drinking coffee, thanks Chris.

Seeing your avatar, I wouldn't think that you are great coffee-lover. Are you sure that you need that particular drink, especially on Friday? Hehe

Seeing Phil's avatar - like some geezer who's been out on the tiles all night, a whole bucket of the stuff seems entirely appropriate!

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

Post #1346462

Phil Parkin

Posted Friday, August 17, 2012 10:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:03 AM
Points: 4,324, Visits: 9,664

I think he needs more than coffee - in fact, I think he had more than coffee ... And plenty of the stuFf too.

But, in the morning, he'll look better than I feel.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1346647

« Prev Topic | Next Topic »

Permissions