identity value on multiple insert

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 2008 Forums

Transact-SQL (2008)

identity value on multiple insert

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 07:02:28

Hi,

I have a statement that does an insert based on 8 default values

insert into PriceList
select @ID, Name, Cost, 1, @UserID, getutcdate()
from PriceListDefaults

Is it possible to get the identity value of each row that's inserted ? There's 8 rows inserted in total.

Thanks for any tips

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-08 : 07:11:05

Yes, it is. Read up on the OUTPUT clause.

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-08 : 07:11:15

You can use the output clause as in:


insert into PriceList
OUTPUT INSERTED.*
select @ID, Name, Cost, 1, @UserID, getutcdate()
from PriceListDefaults

That will do a select and display the values. You can choose to insert it into another table if that is what you need.

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 07:46:16

Thanks for the replies.

My problem seems to be a little more complex than I first thought.

I am doing 3 separate inserts of the 8 default values because there are 3 different categories

There needs to be a relationship between these 3 sets of values. So, I have a table that contains a column for each category.

On the first category I need to insert the identity into it's cat 1.

cat 1 | cat 2 | cat 3
12345 NULL NULL

Then for the second category i need to update the table and insert an identity in the same row as the first category but in the cat 2 column.

cat 1 | cat 2 | cat 3
12345 40405 NULL

And then finally the third time

cat 1 | cat 2 | cat 3
12345 40405 45345

I am doing this because if someone deletes a value from a category it needs to delete it from the other 2 categories. I need to be able to reference those ID's to achieve this.

Does anyone have any advice on how I can achieve this ? Perhaps a table variable that stores the identities from the first insert, then updating that table for identities on the second insert etc.

I hope i have been clear !

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-12-08 : 07:54:40

Sounds like a job for a TRIGGER to me.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 08:08:28

Thanks.

So, should I do the other 2 inserts in the trigger because I'd have the access to the identity value of the first column ?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-08 : 12:18:42

Is what you listed above your actual data model or is that just conceptual for the forum?

That aside, After you insert how are you updating that (those) row(s)? Are you joining to another table? Is this a stored procedure that is being passed mutiple catagories? What is preventing you from doing the insert all in one shot instead of having to do updates after the insert?

It's hard to offer a suggstion without more detail. Certianly, you could capture the OUTPUT of the first insert and use that to perform the updates. Maybe a trigger is the answer, although I highly doubt it from the way I'm reading the description of the problem. Maybe you can help to clarify, in more detail what you are trying to do. Maybe this link will help you with that:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources