Linked column in a table


Here is a technique to include a computed column in a table which will fetch the data from another table based on a reference key.

 

–Create dept table

Create Table CmpDept

(DeptID int,DeptName Varchar(200))

–Insert 3 rows to dept table

INSERT INTO CmpDept

Values (1,’Sales’),(2,’Marketing’),(3,’Production’)

–Create a function to retrive dept name on inputting deptID

CREATE FUNCTION dbo.GetDeptName( @deptID INT)

RETURNS VARCHAR(200)

AS

BEGIN

DECLARE @Text as Varchar(200)

Set @Text = (SELECT DeptName FROM CmpDept

WHERE DeptID=@deptID)

RETURN @Text

END

—Create Employee Table

Create Table CmpEmployee

(EmpID Int,EmpName Varchar(200),EmpAge Int,DeptID Int)

–Adding a computed column for deptName

ALTER TABLE CmpEmployee

ADD DeptName AS dbo.GetDeptName(DeptID)

–Insert data to employees table

INSERT INTO CmpEmployee

Values (1,’Emp1′,30,2),(2,’Emp2′,33,1),(3,’Emp3′,32,2),(4,’Emp4′,35,3),(5,’Emp5′,36,3),(6,’Emp6′,37,3)

–Select Data

Select * from CmpEmployee

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s