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 |
|
Exir
Posting Yak Master
151 Posts |
 Posted - 2009-04-07 : 04:36:12
|
| I have a table which has an int column that is filled as identity specification.In other table i want to relate a row, to a row of the first table and use the Id parameter as foreign key.How can i specify which row of first table should be related to which row of second table?I dont have ID of rows in the first table to put in secind table as foreign key becouse it is set automatically(identity specification)what should i do? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 11:19:27
|
| use OUTPUT operator to populate second table with id values generated in first. |
 |
|
|
Exir
Posting Yak Master
151 Posts |
Posted - 2009-04-07 : 13:07:19
|
quote:
Originally posted by visakh16
use OUTPUT operator to populate second table with id values generated in first.
Could you please explain more? how should i use output operator? forexample the primary key in the first table is ID and foreign key in second table that should be same as ID is PersonalID, then what should i do? how can i insert PersonalID value as same as ID ?Is it posible to set the ID value into a parameter in the first SP and another SP use the content of that parameter? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 13:12:43
|
quote:
Originally posted by Exir
quote:
Originally posted by visakh16
use OUTPUT operator to populate second table with id values generated in first.
Could you please explain more? how should i use output operator? forexample the primary key in the first table is ID and foreign key in second table that should be same as ID is PersonalID, then what should i do? how can i insert PersonalID value as same as ID ?Is it posible to set the ID value into a parameter in the first SP and another SP use the content of that parameter?
no need of two sps you can use single sp itself like belowDECLARE @INSERTED_IDS TABLE(ID int,othercolumns...) INSERT INTO MasterOUTPUT INSERTED.ID,INSERTED.othercolumns.... INTO @INSERTED_IDSSELECT.... values...INSERT INTO Child (...,FK,..)SELECT...,ID,...FROM... tJOIN @INSERTED_IDS iON i.othercolunmns=t.othercolumns.... id is id generated in first table ,fk is fk column where it is to be put |
|
|
|
cycledude
Starting Member
9 Posts |
Posted - 2009-04-07 : 16:42:12
|
| Exir, I think you answered your own question. To get the correct ID's into the "PersonalID" column of the second table, obviously the ID's must exist in the first table. Immediately, after adding a record to the first table, you should be able to detect the ID assigned by querying the ID column like "SELECT @ID = MAX(ID) FROM table1" where @ID is an integer variable that you have DECLAREd. You can then pass this variable into the routine (insert query) which creates the child (or related) records with a stored procedure call that executes an INSERT query, such as: INSERT INTO table2 (column1, PersonalID, ...) VALUES (variable1, @ID, ...). |
|
|
|
|
|
|
|
|
Relation of two tables