Odd Identity Question

Question

Odd Identity Question

Grant Fritchey

SSC Guru

Points: 398886
More actions
December 21, 2007 at 8:34 am

#185179

I recieved an odd question from one of the developers today. He wants to create a table with only one column, an identity column. Then he wants to insert rows, just as a mechanism for generating the next value, a different way of doing the old fashioned sequence number tables. I told him he'd have to have a second column of some sort, but I'm second guessing that. Does anyone know a way to insert a row into a table that only has and identity column? Before you ask, no, you can't use IDENTITY_INSERT because that completely defeats the purpose.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Viewing 11 posts - 1 through 10 (of 10 total)

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

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

You probably thought of this too but just to state the obvious. You could add a column, do the insert(s) then drop the column.

Toni

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

Actually, no, I hadn't thought of that, but since our internally developed applications are never allowed to work under a security model that would provide them with DDL, it's not something that would have occurred to me.

Still, that's one approach. Anyone else?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Simply this

INSERT dbo.MyTable DEFAULT VALUES

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Mark,

Any idea how to insert more than 1 value without a loop? Insert/Select doesn't seem to do it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 5

Yep. Works. Thanks.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 6

> Any idea how to insert more than 1 value without a loop?

Jeff,

Sorry, don't know that one.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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

B Hilderman SSC-Addicted Points: 435 More actions · Answer 7

Jeff Moden (12/21/2007)
Mark,
Any idea how to insert more than 1 value without a loop? Insert/Select doesn't seem to do it...

INSERT dbo.MyTable DEFAULT VALUES

GO

INSERT dbo.MyTable DEFAULT VALUES

GO

INSERT dbo.MyTable DEFAULT VALUES

GO

etc.

😀 sorry, couldn't resist, but as a programmer who uses a hammer when convenient, it's my solution

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 8

Well, if you're lazy you can even get around that:

INSERT INTO dbo.MyTable DEFAULT VALUES

GO 42

I suspect that's not what was meant. I'd be curious if there was a method too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Thanks, Mark... only way I can get it to work for more than 1 row is to have a second column to hold, well, a NULL...

CREATE TABLE dbo.MyTable

(N INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, C CHAR(1))

INSERT INTO dbo.MyTable

(C)

SELECT TOP 100 NULL AS C

FROM Master.dbo.SysColumns

Considering that it allows for multiple row creation, I'm thinking that the extra column might be worth it. That's IF you actually wanted to create a sequence table...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

toniupstny Hall of Fame Points: 3986 More actions · Answer 10

In order to stay in your security scheme, could you put the code to insert the new rows into an sp supplying the number of inserts to do as a paramater. In the sp you add the col, insert the rows, then drop the temp col, return the last inserted row number as an output parm if you like.

I think you could just authorize the app people to exec the proc without giving them or the apps any DDL authority - no?

Toni