stored procedure question 1

[lfc77]

I need to write a stored procedure that inserts a record into 2 separate tables. However, the insert into the second table needs to take the auto incrementing primary key from the record inserted into the first table for one of its fields.

Can anybody tell me how to do this?

Any assistance would be really appreciated.



Cheers,

lfc77

 

[dbomrrsm]

CREATE PROCEDURE TWO_TABLES
AS
INSERT INTO TABLE_ONE (FieldA,FieldB)
--whatever select you have or values
GO
INSERT INTO TABLE_TWO (INCREMENT,FieldA,FieldB)
SELECT * FROM TABLE_ONE
GO

[blue]DBomrrsm[/blue]

 

[tb]

@@Identity

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[lfc77]

This would be the correct way to do this?

CREATE PROCEDURE TESTPROC
(
--PARAMETERS WILL GO HERE
}

AS

DECLARE @DIV AS INTEGER

INSERT INTO DIVISION(CUG, DIV_NAME, SUBCUG, AGENTID, EXDIRECTORY)
VALUES (5790, 'TESTNAME', 1234, 1234, 1)
SELECT @@IDENTITY AS '@DIV'

INSERT INTO ADDRESS(CUG, DIV_ID, ADD_LINE_1, ADD_LINE_2, ADD_LINE_3, TOWN, COUNTY, COUNTRY, POSTCODE, ADDRESS_TYPE)
VALUES (5790, @DIV, 'RD', 'STREET', 'TOWN', 'CITY', 'COUNTY', 'COUNTRY', 'PCODE', 3)

 

[SQLSister]

Except that you should use scope_identity() instead of @@identity. @@identity will give you the wrong indentity in some cases which causes data integrity problems.
use
Set @div = scope_identity()

where lfc77 has
SELECT @@IDENTITY AS '@DIV'

The problem with @@identity arises when you triggers on the table and they insert into another table with an identity. This is the identity that is returned not the one you want. Since you have no control over when such a trigger might be added it is better to avoid the use of @@identity altogether. This is particularly bad because everything will run along smoothly until the it returns an identity value from the seocnd table that don't already exist in the first. THen you will have to figure out which of your records are related to the wrong identity. Can be very bad news to fix.


Questions about posting. See faq183-874

 

[lfc77]

Cheers SQLSister...that could explain some of the problems we've been having with one of our websites.

Thanks,

lfc77

 

[SQLSister]

What really bugs me about this is that pretty much everywhere you look @@identity is the one that people say to use. The many books as well as boards like this recommend @@identity for getting the value to insert into a related table. The way it is written about in BOL, it mentions this, but because it doesn't state it in strong terms or as a possible data integrity problem, most people not realize there is a problem with using it.

Anybody who has ever had to fix a data integrity problem like this knows how miserable it is. Avoidance is the best procedure.


Questions about posting. See faq183-874