RELATIONAL TABLE IDENTITY QUESTION

[ccampbell]

Just a quick question for the gurus in the forum...
I have 1 parent table and 3 child tables that are all related by an identity field in the parent table (sequence_number). I have the following statements in my stored procedure

INSERT INTO PRODUCT (NAME,
NUMBER,
MAKE,
TYPE,
MODEL)
SELECT DISTINCT XML_PRODUCT.NAME,
XML_PRODUCT.NUMBER,
XML_PRODUCT.MAKE,
XML_PRODUCT.TYPE,
XML_PRODUCT.MODEL
FROM XML_PRODUCT

This insert works great on the parent table, but when it is inserted, the identity field for each record inserted is populated with the next available number. My question is, how can I get the identity number from each record inserted to insert into the child tables like this ...



INSERT INTO PRODUCT_DESCRIPTION (SEQUENCE_NUMBER, /*IDENTITY FROM PARENT TABLE */ DESCRIPTION)
SELECT PRODUCT.SEQUENCE_NUMBER
FROM PRODUCT
SELECT XML_PRODUCT.DESCRIPTION
FROM XML_PRODUCT

I know this statement is incorrect, but it will help to illustrate the problem that I am running into. Basically, I need a way to get the identity value of the record I just inserted and put that value along with other values into the child tables. Any help or direction on this would certainly be appreciated.

 

[SQLSister]

Will you be using this to insert only one record ata time or batches of records?

see SCOPE_IDENTITY( ) in books online for what you want to do.
Basically you set a variable to this function immediately after running the insert then use that variable in the insert statement for the next table. This works fine if you are inserting one record at a time.

 

[stevetektip]

Here's two solutions:

1) Use an insert trigger in the parent table. After the row is inserted, there is a table call 'inserted' which is a logical copy of the table just inserted into. You can select the value from the ID column for your child table as follows:

select /*ID column name */ from inserted

<text from MS-SQL Help file>
the deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
SELECT *
FROM deleted
</end of text>

2) Use the @@identity attribute as a parameter after the parent table insert.

<text from MS-SQL Help>
This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
</end of text>

Search for '@@identity' in your MS-SQL help for more details

 

[ccampbell]

My insert statement is used to insert batches of data, not just one row. I am thinking that I will probably have to use triggers ot accomplish this but I am not sure how to set them up and execute them reliably (I am new to triggers but not SQL). Also, I am not sure if that is even the correct way to do things. ANy thoughts?