December 20, 2002 at 11:46 am
Hi,
I have 3 tables linked together (master, detail and subdetail). To save user from typing, I like to be able to copy the current record to another record, so user just need to modify some data in this copied record instead of retyping the whole thing again. I don't know how to do this when I have 3 level of master detail tables. Please help if you ran into this problem before. Thanks alot for your help in advance.
Han Nguyen
December 20, 2002 at 12:00 pm
You need three INSERT with SELECT statements in one transaction. Put it in a stored procedure, of course. Your statements must go in the order master, detail, subdetail to meet referential integrity constraints. Dependng on how you generate your primary key value, you may need to use @@IDENTITY to get the identity value just inserted.
Something like this:
BEGIN TRANSACTION
INSERT tbl_master( ... )
SELECT ...
FROM tbl_master
WHERE col_pk = ...
INSERT tbl_detail( ... )
SELECT ...
FROM tbl_detail
WHERE col_pk = ...
INSERT tbl_subdetail( ... )
SELECT ...
FROM tbl_subdetail
WHERE col_pk = ...
COMMIT TRANSACTION
December 20, 2002 at 2:41 pm
Thanks a lot for your help. I have an idea now.
Han,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply