can you have a table with only an identity col? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

can you have a table with only an identity col?

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 06:11:51

I can create a table that looks like this:

CREATE TABLE staging.GetIdentity (ID BIGINT IDENTITY)

But how would you insert into it? (I don't want to insert an explicit value)

Do I have to create a table like so:
CREATE TABLE staging.GetIdentity (ID BIGINT IDENTITY, Nothing BIT)

INSERT INTO staging.GetIdentity (Nothing)
SELECT 1
SELECT SCOPE_IDENTITY()

There is a very valid reason that I need this, but there is no point in me going into detail (will take too long). Its not really a problem if I can't have a single identity field table, but I'm interested to know whether this is possible.

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-23 : 06:38:33

[code]DECLARE @Sample TABLE
(
ID BIGINT IDENTITY(12342, 1)
)

INSERT @Sample
DEFAULT VALUES

SELECT SCOPE_IDENTITY()[/code]

N 56°04'39.26"
E 12°55'05.63"

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 06:40:42

Thanks Peso!!

Hearty head pats

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-23 : 06:41:51

Well...the only thin I can think of is really sort of a hack because it doesn't actually use the IDENTITY property, but here it is (you'd have to insert the first value manually):

SET IDENTITY_INSERT staging.GetIdentity ON
INSERT INTO staging.GetIdentity (ID)
SELECT MAX(ID) + 1 from staging.GetIdentity
SET IDENTITY_INSERT staging.GetIdentity OFF
SELECT SCOPE_IDENTITY()

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-09-23 : 06:44:22

Ugh, bummer! I tried out a version of Pesos solution before I posted but couldn't get it to work...this was my take:

INSERT INTO staging.GetIdentity (ID) VALUES (default)

- Lumbago

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-09-23 : 07:30:52

Thanks for taking time to help as well Lumbago :)

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources