Id of the last inserted
-
Hi,
I am making an insert in three tables. In the secoond table I need the key previously inserted in table1, and in table 3 he key previously inserted in table2
How can I do that?
Thanks
Daniela
-
Use SCOPE_IDENTITY() after each insert. Something like:
declare @LastID int
insert table1(...) select ...
set @LastID = SCOPE_IDENTITY()
insert table2(Table1ID, ...) select @LastID, ...
set @LastID = SCOPE_IDENTITY()
insert table3(Table2ID, ...) select @LastID, ...
-
1. Insert only if the record with the same values does not exists.
2. Retrieve ID of the record with values inserted(existing).
If you need to use SCOPE_UDENTITY you're doing bad. Your applicaion is just a toy and will not work for real tasks.
_____________
Code for TallyGenerator -
Hi,
I did it (or you did it !
)! Thanks for your help!Daniela
-
Daniella... What did it? Scope_Identity?
Serqiy, surely you are not one of those that believes that every table should have a natural key, are you?
--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) -
I have asked many people and still got no answer:
Why on the earth you would need to insert a row into a table if there is already one with exactly the same set of values?
What DATA you gonna add to you DATABASE with such insert?
Except adding mess, of course.
_____________
Code for TallyGenerator -
Surely where a table has a natural key it is advisable to utilise this as the Primary Key rather then an Identity column.
Identity columns do have their uses but they also have their disadvantages in that duplicate rows can and will occur with no indication that this is the case. There are other pros and cons and the use of an Identity column should be an informed decision rather than the default primary key of a table.
IMHO, since their introduction Identity columns have been used as an easy way out rather than resolving the problems with analysis and design. In most occassions entities will have a natural key and this is usually a better primary key than any surrogate one.
What did we do prior to the introduction of the identity column?
-
The original question was about identities so I answered it.
I agree that you have to think very carefully before using identity as a Primary Key but quite often one has to live with what one has inherited.
I read a post by Erland Sommarskog a few years ago which seemed to take a pragmatic view. I have lost the original reference but I have a copy of part of it which I have pasted below. Even with completely synthetic keys, you could use some sort of random number generator with a check digit.
=====================================
Part of post by Erland Sommarskog:
That is true, that our positions does not seem to on separate ends of
the universe like the people who are making these two kinds of incompatible
statements:
1) Primary keys should ALWAYS be natural keys (and verifiable, whatever
that means, according to Celko).
2) Primary keys should NEVER have any business meaning.
I agree that neither of these positions are tenable, but my position is a
lot more closer to 2, and if you replace NEVER with NORMALLY. However,
the interpretation of 2 may need some modification.
Basically we can identitfy four kinds of keys:
A) Natural keys defined by some other system, like a person identification
number, an ISIN code for a financial instrument, or a currency code.
B) Natural keys defined by our own system. A customer opens an account with
a financial institution, and this account gets an account number
which is defined by our system. The account number is exposed, used
by the users, and can be fed to or from external systems.
C) Completely synthetic keys, which are not exposed to the user, and
which used internally. Often they go in parallel with some alternate
key, (which may be a semi-unique key, and thus not a real key in the
RDBMS sense.)
D) A composite which is composed by keys from other tables, which may of
any of the other sort. This can in fact say be the same as A, even if
all keys are surrogates in the original table.
Keys of type B are not really a problem. Neither are keys of type D
really, although you can see developers who put IDENTITY keys in all
their tables, even those where you have a good composite key. But these
experience often have poor knowledge of database design. And for the
statement that started this thread "no business meaning", and composite
key which consists solely of the C type of keys does not violate this
statement.
So when you use keys of type A? Well, above I had three examples:
1) Persons. In some cases the person identifcation number can be used,
but in many cases not. Many systems do B - that is they assign you a
customer number, and may not even care about your person identification
number. (Which you as a customer may appreciate, if you care about your
privacy.) The system I work with do C - that is we don't expose the
customer number, but the user finds a customer through the name or
the person identification number, and the user will have to sort out
the ambiguities. Most often they probably use the account number,
though, and this is a B type of key. (A customer may have many
accounts, and an account may have many onwers.)
2) Financial instruments. This area is a complete mess, and anyone who
would try to use a natural key would be in for a disaster. There are
several competing global identification schemes, of which none is
whole-covering. So we use an internal ID of type C, but requires the
user to enter a unique name. (Names can change, and there are over
50 table referring to instruments, some of them biggies.) User can
also use ISIN-code to find an instrument.
3) Currency codes. Here it would be a folly to use a key of type C. There
is a commonly used code for currency codes, and even if there are some
people that don't obey them complete (The Brits seems think that there
is something called UKP. There is not.), this is not a big issue. A
country may change its currency, but then usually the value changes
too. For instance Poland went from PLZ to PLN about 10 years ago.
1 PLN = 10000 PLZ, so this affects outstanding values.
And when in doubt - use C.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
-
What did we do prior to the introduction of the identity column?
Oh good grief... here we go again... Yes, I agree... I believe that natural keys should be used as the Primary Key, if possible... but, what do YOU use for a CustID, Bill? SSN? GUID? Some conglomerate of natural data that can be repeated just due to the nature of data thus requiring some sequence, anyway? What?
--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) -
No problems - I know the feeling.
At the time I thought Erland Sommarskog's post succinctly summed up the issues. I just re-posted it as I have found it to be a good heuristic.
-
If you need to use SCOPE_UDENTITY you're doing bad. Your applicaion is just a toy and will not work for real tasks.
Yep, I agree that initial population of data 3 levels deep may be a pretty good indication of impending "Death by SQL", but why have you made the general statment that the use of SCOPE_IDENTITY is a bad thing? What would YOU use for a CustID for new customers?
--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) -
Nope, Ken... none of that is directed at you and I think you did just fine... I think Daniella may have a larger problem if she's got a 3 level (parent, child, grandchild) thing going on during the initial insert and I'm curious what the nay-sayers of IDENTITY column usage use for a CustID or account number for new customers...
--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) -
I use IDENTITY column, of course.
But I never use SCOPE_IDENTITY() to get CustId for table Order.
Because probably this Customer has been inserted when I processed previous order from the same customer.
So, I insert only if the Customer with the same name and identification number does not exists, and than retrieve CustId by [Customer Name]+[Identification Number]. This is my natural key, and IDENTITY value in CustId is just an expression of this natural key, quite handy for effective internal database references.
_____________
Code for TallyGenerator -
Ken was spot on with his post.
I also indicated there is a place for Identity columns but they should be used by design rather than default. Surrogate keys have always existed and all that I am saying is that a Surrogate Key does not need to be an identity column.
Any decision on what to use for a ID column should be very data and design specific. Depending on the system I would initially look at the design to determine that a surrogate key was in fact needed and then use the most appropriate alogorithm to generate the required key, i.e. Erland Sommarskog type C.
Appropriate use of a surrogate key and alogorithm may well have been a better solution for Daniella than the use of the Identity column as the synthetic key generated for each insert would be available for the next.
-
Ok... I feel much better... for a minute, I thought one of my old friends had actually lost his mind...
Daniella...
Serqiy's point is very important... what are you doing to keep the same customer from being entered twice with a slightly different name or address? Just a thought....
--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)
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply