Inserting Identity Only - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Inserting Identity Only

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-27 : 11:59:01

Someone asked me the other day about creating a table with one column that is an identity field and doing an insert so they can capture the SCOPE_IDENTITY() to use across seferal tables. I've never done anything like it and it seemed trivial at the time, but I'm not seeing how to make an insert work. So, for example, we have a table defines as:

DECLARE @Bar TABLE (ID INT IDENTITY(1, 1) NOT NULL)

Now, how do you write a simple insert statement that will add a row to the table?

Basicaly, this is an acedemic exercise, but I can't seem to make it work unless I add another column to the table. Any ideas? Maybe I'm not caffinated enough..? :)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-27 : 12:00:33

[code]Insert into @Bar Default Values[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:06:23

ahh... the default..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-27 : 12:36:29

I swear I tried that.. Thanks for the answer! :)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 12:40:35

quote:
Originally posted by Lamprey

Someone asked me the other day about creating a table with one column that is an identity field and doing an insert so they can capture the SCOPE_IDENTITY() to use across seferal tables. ...

now why would they want to do that? sounds flakey to me.

Then again, what do i know.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 12:42:38

quote:
Originally posted by DonAtWork

quote:
Originally posted by Lamprey

Someone asked me the other day about creating a table with one column that is an identity field and doing an insert so they can capture the SCOPE_IDENTITY() to use across seferal tables. ...

now why would they want to do that? sounds flakey to me.

Then again, what do i know.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

One scenario I can think of is to get the next available ID value.
We do this but we also have a timestamp column and an employeeid column

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 12:56:09

How does the IDENTITY from an @TableVar with session scope help with allocation?

Couldn't it just as easily be done with an @Variable which was incremented after each insert?

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 13:02:22

quote:
Originally posted by Kristen

How does the IDENTITY from an @TableVar with session scope help with allocation?

Couldn't it just as easily be done with an @Variable which was incremented after each insert?

Kristen

I meant the whole concept of using just one-column table and the scenario where it can be used..not so much with a table variable.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:11:48

Ah, OK.

One of this things that I've seen done for that scenario is to have an SProc that will return the "next available number", and then update a single-row, single-column table. So that table is the "keeper" of the AutoNumber.

Then you can modify that for the SProc to give you a range of numbers. So if you know that you will be inserting, say, 50 rows you can ask for the "next 50 numbers". You get the next-available-number back from teh Sproc, but it increments the stored value by 50, instead of just 1.

Although I think the use of IDENTITY is far preferable for this sort of auto-number stuff, this can actually be very useful when you need to know the numbers (e.g. in the application layer, or in an "I want to insert 10 orders and all their order items without having to LOOP

) ahead of time.

They'll be a thread about it on SQL Team somewhere, it's definitely been discussed here ...

Kristen

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 13:16:35

quote:
Originally posted by Kristen

Ah, OK.

One of this things that I've seen done for that scenario is to have an SProc that will return the "next available number", and then update a single-row, single-column table. So that table is the "keeper" of the AutoNumber.

Kristen

Thats exactly what we do.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 13:16:59

And it has its own share of issues when it comes to high transaction applications.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:17:32

Oh well, I least I seem to have been fairly consistent!!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35912
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69572
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52912#178780
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38476#118584

... and probably others too ...

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 13:21:43

quote:
Originally posted by Kristen

Oh well, I least I seem to have been fairly consistent!!

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35912
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69572
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52912#178780
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=38476#118584

... and probably others too ...

good to have consistency.. at least your query plan is not changing frequently

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:44:46

I'm fully cached, but sadly not sufficiently cashed!

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 13:44:48

I'm fully cached, but sadly not sufficiently cashed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources