August 16, 2012 at 1:33 pm
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 (
Idintidentity(1,1),
Descriptvarchar(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.
August 17, 2012 at 1:12 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 17, 2012 at 2:47 am
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.
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
August 17, 2012 at 3:27 am
Dave Ballantyne (8/17/2012)
Hi, this is a bug
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:).
August 17, 2012 at 3:37 am
ChrisM@Work (8/17/2012)
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.
That will teach me to not try and answer questions before drinking coffee, thanks Chris.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 17, 2012 at 3:56 am
...
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::hehe::hehe:
August 17, 2012 at 4:30 am
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::hehe::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!
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
August 17, 2012 at 10:12 am
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply