Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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

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
headbump
Asked:
headbump
1 Solution
 
namasi_navaretnamCommented:
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
 
headbumpAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
namasi_navaretnamCommented:
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
 
DexstarCommented:
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
 
Scott PletcherSenior DBACommented:
Thanks Dex*; yeah, virtual fields are pretty slick, and it's great that MS added them.
0
 
richard_westCommented:
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
 
Scott PletcherSenior DBACommented:
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 free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now