Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-10-22
8
Medium Priority
?
272 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 70

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 375 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 70

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 70

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

688 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