Managing Insert with Identity

Hi,
I am new at stored procedures.
I am running SQL Server 2005 standard on XP Pro sp2.
I have a database that has about 100 tables. For example I have a table
called 'People' with a primary key called 'PeopleID', which is an int and
also of column type 'Identity'. This 'People' table also has a
'PeopleAttributesID' column, also of int nature, and relates to a table name
d
'PeopleAttributes' of which the primary key and Identity column is name
'PeopleAttributesID'. PeopleAttributes table also has a column named
'PeopleInterfaceListID' of int that also relates to a table called
PeopleInterfaceList that has a primary key, int, identity, called
PeopleInterfaceListID.
You get the idea. :-)
My question is how to create a stored procedure for inserting new people in
the 'People' table. My issues is dealing with the identity columns of the
'People' table and it's related tables.
How do I add a row into a table that has an identity primary key and has
foreign keys into tables that also have identity primary keys. Can you give
me an example of how this should be done in your opinion?
Thank you very, very much :)
Antoine Dubuc
MSN Messenger : banquo_ws@.hotmail.com
514-761-1832Antoine wrote:
> Hi,
> I am new at stored procedures.
> I am running SQL Server 2005 standard on XP Pro sp2.
> I have a database that has about 100 tables. For example I have a
> table called 'People' with a primary key called 'PeopleID', which is
> an int and also of column type 'Identity'. This 'People' table also
> has a 'PeopleAttributesID' column, also of int nature, and relates to
> a table named 'PeopleAttributes' of which the primary key and
> Identity column is name 'PeopleAttributesID'. PeopleAttributes table
> also has a column named 'PeopleInterfaceListID' of int that also
> relates to a table called PeopleInterfaceList that has a primary key,
> int, identity, called PeopleInterfaceListID.
> You get the idea. :-)
> My question is how to create a stored procedure for inserting new
> people in the 'People' table. My issues is dealing with the identity
> columns of the 'People' table and it's related tables.
> How do I add a row into a table that has an identity primary key and
> has foreign keys into tables that also have identity primary keys.
> Can you give me an example of how this should be done in your opinion?
> Thank you very, very much :)
> Antoine Dubuc
> MSN Messenger : banquo_ws@.hotmail.com
> 514-761-1832
Youinsert by leaving off the identity column. Once the insert is complete,
you can get the new value using the SCOPE_IDENTITY() function. if you need
to insert FK values, you need to pass them into the stored procedure and use
them in the insert. You'll need to get those values from your application
first.
David Gugick
Quest Software|||Hi David,
When I try to execute this, I get the cannot leave null for this column erro
r.
DI have to manually insert it using something like:
DECLARE PeopleID INT
PeopleID = SCOPE_IDENTITY
...
Exactly how do you do this?
thank you,
Antoine
"David Gugick" wrote:

> Antoine wrote:
> Youinsert by leaving off the identity column. Once the insert is complete,
> you can get the new value using the SCOPE_IDENTITY() function. if you need
> to insert FK values, you need to pass them into the stored procedure and u
se
> them in the insert. You'll need to get those values from your application
> first.
> --
> David Gugick
> Quest Software
>
>|||> When I try to execute this, I get the cannot leave null for this column
> error.
> DI have to manually insert it using something like:
> DECLARE PeopleID INT
> PeopleID = SCOPE_IDENTITY
Did you mean:
DECLARE @.PeopleID INT
... INSERT statement here
SET @.PeopleID = SCOPE_IDENTITY()
?|||>> have a database that has about 100 tables. For example I have a table ca
lled 'People' with a primary key called 'PeopleID', which is an int and also
of column type 'Identity'. This 'People' table also has a 'PeopleAttribute
sID' column, also of int na
ture, and relates to a table named 'PeopleAttributes' of which the primary k
ey and Identity column is named 'PeopleAttributesID'. PeopleAttributes table
also has a column named 'PeopleInterfaceListID' of int that also relates to
a table called PeopleInter
faceList that has a primary key, int, identity, called PeopleInterfaceListID
. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
However, these data element names and the improper use of IDENTITY as a
key imply that you have never written an RDBMS before. IDENTITY is not
a key by definition and you are mixing data and metadata. It looks
like the typhical newbie disaster with IDENTITY columns used as pointer
chains to mimic a 1970's IDMS database. Please get help instead of
newsgroup kludges.|||In order for a column with the IDENTITY 'property' to become a key then you
need only make it such using a CONSTRAINT, either PRIMARY KEY where no
natural key exists or UNIQUE if you are making it an artificial or surrogate
key.
Get a grip and be more constructive, the poster said his level of experience
was beginner so be polite and don't rant your rude rubbish.
There are many objects in the world that do not have a natural key, my
favourite is the message board example.
It's quite concievable that the author wants to create an auto generated
number and use that as a key, afterall, its becoming very dodgy to say the
least in the US using social security numbers and capturing that info on a
website, well - would you honestly give over your national insurance number
(uk equiv) to a company - nope.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132258326.365651.286720@.g14g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> However, these data element names and the improper use of IDENTITY as a
> key imply that you have never written an RDBMS before. IDENTITY is not
> a key by definition and you are mixing data and metadata. It looks
> like the typhical newbie disaster with IDENTITY columns used as pointer
> chains to mimic a 1970's IDMS database. Please get help instead of
> newsgroup kludges.
>