how to populate foreign key field with data

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to populate foreign key field with data

Author

Topic

gridview
Starting Member

11 Posts

Posted - 2008-11-11 : 14:51:43

I have table called A with a primary key called Id.
I have another table called B. Both tables A and B have no of fields and data in it.

Now due to sudden change in the application we need to build relation between table A and B as below:

Table B will have a new column called AId which will be foreign key and will be key to connect table A primary key Id.

Now the problem is how to write a sql script to populate the data in AId column of child Table B same as data in Id column of parent Table A?

Please suggest.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-11 : 16:01:19

It depends on what the ID column is. If it's a identity column, then you can use Scope_Identity() to recover the last inserted identity.

here's a copy and pase that explains ways to grab the inserted ID

***********
In SQL Server 2000, have you ever wanted to immediately query a table after an INSERT or UPDATE statement, to retrieve the value of a column affected by the original statement? It may be the result of a calculated column, or the value of an identity column. or some other default value. Typically you'd have to issue a subsequent SELECT statement, either in a stored procedure or with another round-trip call from your application back to the server. Also, in SQL Server 2000, have you ever wanted to get immediate 'old value-new value' feedback when you UPDATE data? Often, you'd need to tap into the INSERTED and DELETED system tables, which were only visible in a database trigger.

For example, in SQL Server 2000, if we want to determine the value of an IDENTITY column after an INSERT, we'd often use the SQL Server SCOPE_IDENTITY function:

DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
INSERT INTO @tTestTable VALUES ('steve Goff')
SELECT SCOPE_IDENTITY()

SQL Server 2005 provides a new OUTPUT statement that allows developers to accomplish these tasks more conveniently and effeciently. Using OUTPUT in conjunction with an INSERT/UPDATE statement, we can easily read the information added/changed. Instead of using SCOPE_IDENTITY, we can immediately OUTPUT the value of the identity column:

DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
INSERT @tTestTable OUTPUT Inserted.MainPK VALUES ('steve Goff')

If you issue multiple INSERT statements and need a list of the insertions afterwards, you can direct the OUTPUT to a table:

DECLARE @tTestTable TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL, Name Char(50))
DECLARE @tTemp table (mainpk int)
INSERT @tTestTable OUTPUT Inserted.MainPK into @tTemp VALUES ('Kevin Goff')
INSERT @tTestTable OUTPUT Inserted.MainPK into @tTemp VALUES ('steve Goff')
SELECT * FROM @tTemp

Next - if you issue an UPDATE statement and want immediate access to the INSERTED and DELETED system tables to see the new value/old value for specific columns, you can do the following:

DECLARE @tTest TABLE ( MainPK [int] IDENTITY(1,1) NOT NULL ,Amount decimal(10,2))
INSERT INTO @tTest VALUES (100)
INSERT INTO @tTest VALUES (200)
INSERT INTO @tTest VALUES (300)

UPDATE @tTest SET Amount = Amount * 10
OUTPUT DELETED.MainPK, DELETED.Amount AS OldValue, INSERTED.Amount AS NewValue

And again, if you want to direct the OUTPUT results to a table variable, you can do the following:

DECLARE @tTemp TABLE (MainPK int, OldValue Decimal(10,2), NewValue Decimal(10,2))

UPDATE @tTest SET Amount = Amount * 10
OUTPUT DELETED.MainPK, DELETED.Amount AS OldValue, INSERTED.Amount AS NewValue INTO
@tTemp

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 23:54:11

for existing data updation, do you have any other field in B which corresponds to table A's fields? if yes, you can use an update statement joining on the two columns to get PK value from A to go into fk column of B.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-12 : 08:59:55

Exactly. You need to identify a single (unique) A for each B. Once you know that then it is simply an update statement
update b set fk=(select a.id from a where a.??=b.?? .....other conditions...)

Consider though that if you can do this you already have the relationship you need and may just be creating a surrogate key on some columns that should already be unique.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources