Insert statement after adding a column

Question

Insert statement after adding a column

hogpen

SSC Eights!

Points: 931
More actions
January 22, 2013 at 9:29 am

#287924

This seems like a really simple thing to do, but I need some help.
I currently have a table with 2 main columns, fee type code and margin, that looks like:
FeeTypeCode Margin
ADJ Gathering Revenue
ADMN Gathering Revenue
CASH Processing Revenue
GATH Gathering Revenue
MIN Processing Revenue
I've added an additional column, called FeeTypeLevel1.
How to I insert values into the new column based on the Margin column
I want the altered table to look like:
FeeTypeCode Margin FeeTypeLevel1
ADJ Gathering Revenue Gathering Fee
ADMN Gathering Revenue Gathering Fee
CASH Processing Revenue Processing Fee
GATH Gathering Revenue Gathering Fee
MIN Processing Revenue Processing fee
Thanks in advance, let me know if I can clarify.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

The main you have not yet received a response is because your question has no details. We need ddl and sample data at a bare minimum. Please read the article at the first link in my signature for best practices when posting questions.

Taking your sparse description and sample I came up with this.

create table #Table

(

FeeType varchar(10),

Margin varchar(50)

)

insert #Table

select 'ADJ', 'Gathering Revenue' union all

select 'ADMN', 'Gathering Revenue' union all

select 'CASH', 'Processing Revenue' union all

select 'GATH', 'Gathering Revenue' union all

select 'MIN', 'Processing Revenue'

alter table #Table

add FeeTypeLevel1 varchar(50)

update #Table

set FeeTypeLevel1 = replace(Margin, 'Revenue', 'Fee')

select * from #Table

drop table #Table

This seems to work for your sample but no real clues if it is correct.

I would recommend that you look at normalizing your data. Maybe have a MarginType table or something so you don't have the full description repeated over and over?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

hogpen SSC Eights! Points: 931 More actions · Answer 2

That makes sense, sorry for the lack of details. Thanks!