How to get identity from parent Table and insert it into the child

Leaving aside for the moment your table schema, which seems a bit odd, This
is an example of where using Surrogate keys can be a bit trickier than using
meaningful keys... In order to do this is robust way, you must have some
mechanism (Other than the Surogate Identity Key) to "connect" the child
records to the right parent record.
Now to the table schema... regarding Your "Child" table, the one you have
named TBCOURSE, from the name, it would seem to contain Course data... But i
f
that's true, it makes no sense to have a FK in it that points to one row in
a
table which contains Students.. That implies that for each course, there is
but one and only one student, or some single student that plays some special
distinct role related to that course... SO, since that makes no sense, the
next logical question is
Exactly what do the rows in these tables represent? nd then...
What exactly are you trying to do with this insert ?
"RayAll" wrote:

> I have a table with the following schema
> F_NAME L_NAME COURSE_ID COURSE_DESC
> this table contains 10 records ,and I need to insert these records into 2
> tables .TBSTUDENT and TBCOURSE.
> in TBSTUDENT ,I have student_id which is an identity column and in TBCOURS
E
> ,I have a foriegn key to this field. How can I isert 10 records from the
> first table into TBSTUDENT AND TBCOURSE ,where I should first get the
> identity generated from the first insert and use it in the second
> insert(into TBCOURSE).
> Thanks
>
>Thanks for your reply.
No,The schema of the table is not odd ,because this is a buffer table which
I amusing in a DTS package to read records from a CSV file into this table
(and do the validation during transformation) and then from here I have to
distribute the records into the relavant tables.Dose it still seem to be
odd?
I made the chema myself ,because I wanted to simplify things here are the
schema of the 3 other tables I am trying to insert into.
**************CLIENT:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PE_CLI_FK]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PROGRAM_ENROLLMENT] DROP CONSTRAINT PE_CLI_FK
GO
CREATE TABLE [dbo].[CLIENT] (
[INTERNAL_ID] [numeric](12, 0) IDENTITY (1, 1) NOT NULL ,
<--this is the identity field
[CLIENT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USERNAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
.....
) ON [PRIMARY]
GO
***********PROGRAM_ENROLLMENT:
CREATE TABLE [dbo].[PROGRAM_ENROLLMENT] (
[PROGRAM_ENROLLMENT_ID] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[INTERNAL_ID] [numeric](12, 0) NOT NULL , <--This is
foriegn key to the Client Table
...
) ON [PRIMARY]
GO
***********PERSONAL_INFO:
CREATE TABLE [dbo].[PERSONAL_INFO] (
[FIRST_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[MIDDLE_NAME] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
...
[INTERNAL_ID] [numeric](12, 0) NOT NULL , <--this is also a foriegn
key to Client
...
[Timestamp] [timestamp] NOT NULL
) ON [PRIMARY]
GO
******************
Here is what I'm trying to do:
I am trying to get each record from my buffer table and insert it into these
three tables,but before inserting into the Programe_enrolment and
Personal_info ,I have to have the InternalID of the Client Table,then I
should use the internalID in every other table I insert the rest of the
record.
For instance let's say this is my record in buffer table:
FirstName clientID UserName
Ray 1233 ray5531 ............
First I insert this into client and I get the internalID of for instance
1000 ,then I need to use this and insert other fields of this record into
Personal_Info and PROGRAM_ENROLLMENT.
Same process for all the other records in the buffer table.
Thanks for your time.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A31711F6-1A30-4DDC-9741-5EF81D96CE62@.microsoft.com...
> Leaving aside for the moment your table schema, which seems a bit odd,
> This
> is an example of where using Surrogate keys can be a bit trickier than
> using
> meaningful keys... In order to do this is robust way, you must have some
> mechanism (Other than the Surogate Identity Key) to "connect" the child
> records to the right parent record.
> Now to the table schema... regarding Your "Child" table, the one you have
> named TBCOURSE, from the name, it would seem to contain Course data... But
> if
> that's true, it makes no sense to have a FK in it that points to one row
> in a
> table which contains Students.. That implies that for each course, there
> is
> but one and only one student, or some single student that plays some
> special
> distinct role related to that course... SO, since that makes no sense,
> the
> next logical question is
> Exactly what do the rows in these tables represent? nd then...
> What exactly are you trying to do with this insert ?
> "RayAll" wrote:
>|||Your table design is incomplete because you haven't declared natural keys on
these tables. Declare the proper keys and it's easy to do what you require.
See this example:
http://www.google.co.uk/groups?selm...%40giganews.com
On the other hand, replacing one surrogate key with another seems
particularly pointless anyway.
David Portas
SQL Server MVP
--|||Thanks for your reply.
Where am I replacing surrogate key with another one.I have only one
surrogate key in client and I am using this in other tables to maintanin a
link between client and other informations.What's wrong with this?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0t-dner_57mHRdTfRVn-vg@.giganews.com...
> Your table design is incomplete because you haven't declared natural keys
> on these tables. Declare the proper keys and it's easy to do what you
> require. See this example:
> http://www.google.co.uk/groups?selm...%40giganews.com
> On the other hand, replacing one surrogate key with another seems
> particularly pointless anyway.
> --
> David Portas
> SQL Server MVP
> --
>|||What's wrong is that IDENTITY should never be the *only* key of a table. By
definition IDENTITY should always be redundant and breaking that rule
destorys integrity. For example, you don't want duplicate clients with the
*same* Client_ID and Username. Declare the keys on the other columns and
your problem is solved. Take a look at the example I posted.
David Portas
SQL Server MVP
--|||Symantically ,there is a compund key there ,which I can rely on.However It
hasn't been created,but technically there is no record with same "ClientID"
and "Affiliate_ID".
Yes,your answer was really helpfu.I think that's the way to do so.
Thanks
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OZadncsqxMs9QNTfRVn-jw@.giganews.com...
> What's wrong is that IDENTITY should never be the *only* key of a table.
> By definition IDENTITY should always be redundant and breaking that rule
> destorys integrity. For example, you don't want duplicate clients with the
> *same* Client_ID and Username. Declare the keys on the other columns and
> your problem is solved. Take a look at the example I posted.
> --
> David Portas
> SQL Server MVP
> --
>