Concatenate existing fields SQL server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Concatenate existing fields SQL server

Concatenate existing fields SQL server

Rate Topic

Display Mode

Topic Options

Author

Message

middletree

Posted Tuesday, June 23, 2009 9:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, 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.

Post #740288

NicHopper

Posted Tuesday, June 23, 2009 9:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 1,306, Visits: 1,420

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/

Post #740295

NicHopper

Posted Tuesday, June 23, 2009 9:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 1,306, Visits: 1,420

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/

Post #740298

middletree

Posted Tuesday, June 23, 2009 10:00 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, 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

Post #740335

NicHopper

Posted Tuesday, June 23, 2009 10:03 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:18 AM
Points: 1,306, Visits: 1,420

Option 1 would certainly be simpler.

Happy to help.

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

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Post #740338

middletree

Posted Tuesday, June 23, 2009 12:35 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 09, 2010 7:58 PM
Points: 121, Visits: 195

It seems to have worked. Thanks.

Post #740460

« Prev Topic | Next Topic »

Permissions