Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Does/can OUTPUT clause turn identity_insert on & off Expand / Collapse
Author
Message
Posted Thursday, August 16, 2012 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 25, 2014 1:37 PM
Points: 13, Visits: 154
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
Posted Friday, August 17, 2012 1:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1346360
Posted Friday, August 17, 2012 2:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:03 AM
Points: 1,949, Visits: 8,303
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
Posted Friday, August 17, 2012 2:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
Phil Parkin (8/17/2012)
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.


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
Posted Friday, August 17, 2012 3:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062


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 ).


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1346420
Posted Friday, August 17, 2012 3:37 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
ChrisM@Work (8/17/2012)
Phil Parkin (8/17/2012)
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.


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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1346427
Posted Friday, August 17, 2012 3:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

...
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?


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1346439
Posted Friday, August 17, 2012 4:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:51 AM
Points: 7,127, Visits: 13,503
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?


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

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 #1346462
Posted Friday, August 17, 2012 10:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1346647
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse