Solved

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

Posted on 2003-10-22
8
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Scott Pletcher
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
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:Scott Pletcher
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:Scott Pletcher
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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