Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenate existing fields SQL server Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 9:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 9, 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
Posted Tuesday, June 23, 2009 9:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:10 AM
Points: 1,507, Visits: 1,685
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
Posted Tuesday, June 23, 2009 9:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:10 AM
Points: 1,507, Visits: 1,685
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
Posted Tuesday, June 23, 2009 10:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 9, 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
Posted Tuesday, June 23, 2009 10:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:10 AM
Points: 1,507, Visits: 1,685
Option 1 would certainly be simpler.

Happy to help.


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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #740338
Posted Tuesday, June 23, 2009 12:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 9, 2010 7:58 PM
Points: 121, Visits: 195
It seems to have worked. Thanks.
Post #740460
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse