Solved

Combining two field values as the default value for another field.

Posted on 2003-10-22
8
228 Views
Last Modified: 2008-03-06
Hi all,

I have a field called strFullName (SQL 7) that I wish to populate by combining the fields strFName and strLName separated by a space.

Do I do this with ALTER TABLE ALTER COLUMN??

If so, where do I create this statement and what is the syntax I should use. BOL suggests using this method, but I know so little about the language and physical db structure that I can't figure out where to begin.

Thanks,

A
0
Comment
Question by:headbump
8 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9599592
You can use insert and update triggers to accomplish this.

CREATE TRIGGER tiu_Table
ON Table
FOR INSERT, UPDATE

If Exists(Select 1 from Inserted)
BEGIN
   Update Table
     Set FullName = i.LastName + ', ' + i.FirstName
   FROM inserted i
   WHERE Table.pk_id = i.pk_id // pk_id is the primary key
 

END

GO
0
 

Author Comment

by:headbump
ID: 9599764
NAMASI_NAVARETNAM,

Thanks for the response...I've tried your suggestion but get an error when I do this:

CREATE TRIGGER tiu_tblEngineer
ON tblEngineer
FOR INSERT, UPDATE

If  EXISTS (strLName)
BEGIN
   Update tblEngineer
     Set strFullName = i.strLastName + ', ' + i.strFirstName
   FROM inserted i
   WHERE tblEngineer.intEngineerID = i.intEngineerID



Incorrect syntax near the keyword If
Incorrect syntax near intEngineerID

Thanks
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9599780
You should use a computed column *instead*, like so:

ALTER TABLE yourTableName
ADD strFullName AS strFName + ' ' + strLName

Reasons:
1) You avoid the overhead of a trigger
2) The new column is not stored in the table, so does not take any disk space
3) You can reference the new column name in any SELECT query just as would an actual column name
4) If necessary, you can change the column definition in one place and it will immediately take effect, with no other UPDATEs being required.

0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 125 total points
ID: 9600033
Try this,

CREATE TRIGGER tiu_tblEngineer
ON tblEngineer
FOR INSERT, UPDATE
AS
BEGIN

If  EXISTS (Select 1 from Inserted)
BEGIN
   Update tblEngineer
     Set strFullName = i.strLastName + ', ' + i.strFirstName
   FROM inserted i
   WHERE tblEngineer.intEngineerID = i.intEngineerID
END

END
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 19

Expert Comment

by:Dexstar
ID: 9600062
I was going to suggest that you use a view that displays the combined values, but Scott just totally schooled me.  Geez!  I didn't even know you could do that!

Scott, I'm very impressed.

My $0.02,
Dex*
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9600156
Thanks Dex*; yeah, virtual fields are pretty slick, and it's great that MS added them.
0
 
LVL 1

Expert Comment

by:richard_west
ID: 9600297
Scott --

Can you clairify about how the new computed column does not require any addtional disk space?

Also, can you index on the computed column?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9600872
With certain reasonable restrictions you can, and in this case I'm almost certain you could.  See "Creating Indexes on Computed Columns" in BOL for more details.

It doesn't take up space because it's not actually derived until and unless you reference it in the query.  We know that SQL can "materialize" a new column as part of a query, that is:

SELECT colA + colB AS colC
FROM ...

well, this is essentially the same thing except that the expression has been pre-defined to SQL Server.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now