August 31, 2006 at 12:43 pm
Hi,
I'm trying to figure this out. I have a master-child relationship I do an initial load extract the identity field and then reload to the child. This works find if I'm loading the data initially however I squash the first id in the child if I load a new relationship.
I load the master record without any issues, however when I start loading other values with different budgetid I'm always getting the most recent id.
Thanks in advance
Example:
| Master Table | Child Table | ||||||
| BudgetID | Description | PrimaryID | BudgetID | Value | |||
| 1 | Some data | 1 | 1 | 2 | |||
| 2 | Some more data | This loads OK | 2 | 1 | 4 | ||
| 3 | 2 | 5 | Expected results | ||||
| 4 | 2 | 6 | |||||
| 1 | 2 | 2 | What I'm getting | ||||
| 2 | 2 | 4 | |||||
| 3 | 2 | 5 | |||||
| 4 | 2 | 6 |
BEGIN
INSERT INTO transactional_gldepartment_budgetlist(gldepartment,gldepartmentname,budgetyear,entrydate)
VALUES(@DPTNO,@DPTNAME,@YEAR,@ENTRYDATE)
SELECT @BudgetID = @@IDENTITY
BEGIN
INSERT INTO transactional_deptbudget(budgetaccount,budgetaccountname)
SELECT DISTINCT budgetaccountnumber,budgetdescription FROM lookup_budgetaccounts WHERE budgetaccountnumber != 0
UPDATE transactional_deptbudget SET dbudgetid = @BudgetID WHERE ((dbudgetid != @BudgetID)OR (dbudgetid IS NULL))
END
END
August 31, 2006 at 1:00 pm
Try this:
BEGIN
INSERT INTO transactional_gldepartment_budgetlist(gldepartment,gldepartmentname,budgetyear,entrydate)
VALUES(@DPTNO,@DPTNAME,@YEAR,@ENTRYDATE)
SELECT @err = @@error
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
ELSE
BEGIN
SELECT @BudgetID = SCOPE_IDENTITY( )
INSERT INTO transactional_deptbudget(dbudgetid,budgetaccount,budgetaccountname)
SELECT DISTINCT @BudgetID,budgetaccountnumber,budgetdescription FROM lookup_budgetaccounts
WHERE budgetaccountnumber != 0
END
END
I use SCOPE_IDENTITY( ) cos if there are any triggers for the underlying tables and if the trigger generates Identity values it will pick up the identity generated by the SQL Insert instead of triggers.
Hope this helps
Thanks
Sreejith
August 31, 2006 at 1:26 pm
Thanks Sreejith works like a charm!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply