SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Concatenate existing fields SQL server


Concatenate existing fields SQL server

Author
Message
middletree
middletree
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 195
I have a table with static data. For reasons way too long to go into, I need to add a column, and the value of that column will need to be a concatenation of two other existing fields in that same table. It's got something like 225000 rows, so I cannot do it manually. I'm googling and not seeing much that will help on this. I imagine it's a simple T-SQL statement, but am drawing a blank.

Using SQL Server 2005.

To be clear: I am not talking about selecting from the table and doing the concatenating on the fly. I want to add a column.
NicHopper
NicHopper
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2170 Visits: 1907
Hi,

I think you have two options here,

1. If the table is truly static (never going to change) then you could just run an update on the table to set column C to be equal to column A and B

2. If the data is likely to change in the future then you could use a computed column.

Hope this helps.

------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/
NicHopper
NicHopper
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2170 Visits: 1907
Here is an example of the computed column (incase you need it)

USE tempdb
GO

CREATE TABLE TableA
(
ColumnA VARCHAR(5),
ColumnB VARCHAR(5),
ColumnC AS ((ColumnA+'-')+ColumnB)
)

INSERT INTO TableA
(ColumnA,ColumnB)

VALUES
('Hello','World')

SELECT ColumnA,
ColumnB,
ColumnC
FROM TableA

DROP TABLE TableA

------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/
middletree
middletree
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 195
I think Option 1 is best. I added a column and will now do an update. First, I have to cast the one column (which is numeric) as a varchar.

thanks
NicHopper
NicHopper
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2170 Visits: 1907
Option 1 would certainly be simpler.

Happy to help.

------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/
middletree
middletree
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 195
It seems to have worked. Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search