Adding ID's to related entities...

[jtrembla]

Does anyone know of an elegant way I can add NewId's from the parent table to all related tables?

I am forecasting items and if a new item is added to the main table then all the other tables need to have that id in it so the user knows that data must be entered for that entity. I have many related tables, many of which are forecast adjustments.

For example

Items Table
ItemID | Name | Type
---------------------
1 Drug1 Brand
2 Drug 1 generic



Forecast Table - The timeline is setup by the user and is stored in another table.

Item ID | Date | Value
=========================
1 1/1/2008 102
1 2/1/2008 34
1 3/1/2008 939
2 1/1/2008
2 2/1/2008
2 3/1/2008

 

[SQLSister]

If your id is an identity then use scope identity to grab the value just inserted and then insert records to other tables.

declare @id  int
insert table1 (test1, test2)
values ('1', 'mytest')
set @id = scope_identity()
insert table1 (Myid)
values (@id)

Of cousre if you are inserting to multiple tables you will need the values for all required fields and you should wrap all this up in a transaction to manitain data integrity.



"NOTHING is more important in a database than integrity." ESquared

 

[jtrembla]

I figured I had to do this, I was hoping sql sever had something built into relationships that was similiar to cascade delete, but the opposite, like cascade add, lol.