Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CaringHomesGuru
Starting Member
2 Posts |
 Posted - 2011-11-11 : 05:16:50
|
| Hi all, I am new to sql server and have come accross an issue when designing a database. I have a tbl_people that is the main table for any person's details that will be entered into the system. The PK for this tbl is Person_Code and is a 6 character int. The problem is that I have Person_Code in many other tbl's within the databse as a FK so that I can create relationships between the tbls. The problem I am having is: How do set up the db [and where exactly] so that when a user enters a new "person" into the system the "Person_Code" is automatically populated/cascaded to the other tables where it is used as a FK. I have got the system set up so that Person_Code is auto-populated and incremented by 1 in the tbl_people.Thanks for your help. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-11 : 05:21:29
|
| If you are inserting row by row and the id is an identity then use scope_identity() to get the value to insert into other tables.If you are inserting in batches then use the output clause to get the ids associated with the rows.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
CaringHomesGuru
Starting Member
2 Posts |
Posted - 2011-11-11 : 05:45:23
|
Thanks nigelrivett for the response. This looks exactly what I want. How do I go about implementing this?thanks. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-11 : 06:00:35
|
| declare @id int insert mytbl (...) select ....select @id = scope_identity()insert othertable (mytblfk, ...) select @id, ...==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-11 : 06:15:42
|
| if you're planning for batch population you may need to have look at OUTPUT clause to get generated id values from pk table and pass it onto fk fields of other tables using a table variable------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-11 : 06:22:47
|
| >> If you are inserting in batches then use the output clause to get the ids associated with the rows.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Auto-populate FK with the PK from another Table