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 |
|
johnmck
Starting Member
3 Posts |
 Posted - 2009-12-08 : 22:27:22
|
| Hi, I'm putting together a database which has about 10 tables. I'm trying to figure out how do you populate your FK of one table that referrences a PK from another? For example I have a suppliers table and SupplierID is my PK which is being automatically incremented when I enter new records. A second table (Orders) references the Suppliers table with a FK from the Orders table. Do I use some type of function to pull the data? Maybe do a lookup table? These are some options I found but have no idea how to do. I guess entering each manually would be another way but that seems like a lot of work when it seems I can set something up to carry over the PK to the FK field. Sorry for such a simplified question just started hitting SQL Server 2005 seriously about a month ago. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-12-08 : 22:36:41
|
| Here's an example:CREATE TABLE Table1 (c1 int identity(1, 1), c2 varchar(10))CREATE TABLE Table2 (someColumn int, c1 int, someOtherColumn datetime)DECLARE @v1 varchar(10)INSERT INTO Table1 (c2) VALUES('Tara')SET @v1 = SCOPE_IDENTITY()INSERT INTO Table2 (someColumn, c1, someOtherColumn) VALUES (1, @v1, GETDATE())SELECT * FROM Table1SELECT * FROM Table2DROP TABLE Table1, Table2Hope this helps.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
johnmck
Starting Member
3 Posts |
Posted - 2009-12-09 : 10:01:28
|
| Tara,Thanks for the quick feedback. You have given me some good stuff to go on. Now will I set up a trigger (using some of the code you sent) to kick off when populating the PK from the one table to immediately populate it's corresponding FK value in another table?-John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
johnmck
Starting Member
3 Posts |
Posted - 2009-12-09 : 11:51:32
|
| If not a trigger what actually kicks the code into motion to update my FK in table2 everytime a new record is entered into table1 where my PK resides.Sorry I know these seem to be obvious questions. Unfortunately I'm still very green at this stuff.Thanks,John |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Updating FK from a PK