Add Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Add Column

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-08 : 21:00:35

Hi T-Sql Gurus,

Here is my table "State"
ID,TYPE,STATE
1,B,CA
2,C,GA
3,D,NY

Here what i want (Same Table "State")
ID,TYPE,STATE,COLUMNXY
1,B,CA,B-CA
2,C,GA,C-GA
3,D,NY,D-NY

Please guide me how i can do this one through t-sql. Thanks in advance.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 21:42:05

To add it to the table as a computed column:

ALTER TABLE [State] ADD [COLUMNXYX] as [Type] + '-' + [State]

TO select it

SELECT ID,TYPE,[State],[COLUMNXYZ] = [Type] + '-' + [State]
FROM [State]

You should rename the table. You really don't want the table to have the same name as column or vice-versa.
'

Poor planning on your part does not constitute an emergency on my part.

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-08 : 21:59:07

Thanks for your quick reply,
ALTER TABLE [State] ADD [COLUMNXYX] as [Type] + '-' + [State]

TO select it

SELECT ID,TYPE,[State],[COLUMNXYZ] = [Type] + '-' + [State]
FROM [State]

Could you please explain me,this is my whole statement?
i want to keep the same table "State" i can't del/rename it.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-08 : 22:41:50

[code]
ALTER TABLE [State] ADD [COLUMNXYX] as [Type] + '-' + [State]
[/code]

The above command adds a column to the [State] table, and defines it is the concatonation of the Type and State fields in that same table.

[code]
SELECT ID,TYPE,[State],[COLUMNXYZ] = [Type] + '-' + [State]
FROM [State]
[/code]

This statement would simply present the new column without actually storing it.

As far as renaming the table..."State" is a keyword. It likely shows up in blue in your statements if not enclosed in brackets. It is just a good idea to to avoid keywords in column or table names.

Also, best practice would be to not have a column in a table with the same exact name. better to name the table "States" and name the column "StateName" or something.

This makes it easier to use later on. If you were to write down, or someone to ask "I need the state info"....that could mean either the table OR the column. If you can't change the column name, not a big deal

Poor planning on your part does not constitute an emergency on my part.

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-08 : 22:56:10

Thanks for your help. Answered!

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-08 : 23:08:49

Quick Question "data"

SELECT ID,TYPE,[State],[COLUMNXYZ] = [Type] + '-' + [State]
FROM [State]

after i run this query, everything look fine, but how i can keep these changes.
select * from state (COLUMNXYZ IS NULL)

jscot
Posting Yak Master

106 Posts

Posted - 2011-02-08 : 23:10:00

I know i can use INTO syntax to copy to other column. But i want to keep same table "State"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-09 : 04:57:22

quote:
Originally posted by jscot
after i run this query, everything look fine, but how i can keep these changes.
select * from state (COLUMNXYZ IS NULL)

Well...it would probably help a great deal to *read* :) The answer has been given to you before. I'll give you a hint: it has something to do with ALTER TABLE.

- Lumbago
My blog-> www.thefirstsql.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-09 : 08:23:29

SELECT * FROM
(
SELECT ID,TYPE,[State],[COLUMNXYZ] = [Type] + '-' + [State]
FROM [State]
) as t
where COLUMNXYZ IS NULL

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources