The Identity Select

Question

The Identity Select

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
January 10, 2019 at 8:14 pm

#100991

Comments posted to this topic are about the item The Identity Select

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72617 More actions · Answer 1

Nice one to end the week on, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

morlindk SSCrazy Points: 2294 More actions · Answer 2

Elegant!
- Yet, if I only wanted to add an identity column, in order to keep the table name (and save on storage), I would use simply
ALTER TABLE dbo.Names
ADD id INT IDENTITY(1, 1)

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 3

thought it would error so tested and was pleasantly surprised,
will have to remember this one for future
thanks Steve

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

Budd Hall of Fame Points: 3781 More actions · Answer 4

Very interesting, but again it gets me thinking - I hate when that happens.... 🙂
What is the difference between these 2 variations of today's QOD and how would you change the first one to make it as good at the second. Of course I'm talking about field length.
SELECT IDENTITY(INT, 1,1) AS id,bt.FirstName
INTO CustomerNames
FROM (SELECT * FROM (VALUES ('Steve'), ('Andy'), ('Brian')) AS Names(FirstName)) AS bt
---------------
CREATE TABLE Names
(firstname VARCHAR(100));
GO
INSERT dbo.Names (FirstName) VALUES ('Steve'), ('Andy'), ('Brian');
GO
SELECT IDENTITY(INT, 1,1) AS id,bt.FirstName
INTO CustomerNames
FROM Names AS bt

The only thing I noticed was varchar(5) as opposed to varchar(100).

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 5

From the identity perspective, I don't think anything. either SELECT INTO is what it does. If you use it from different sources, you might get different results.