New column data - 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

General SQL Server Forums

New to SQL Server Programming

New column data

Author

Topic

sharankruthi
Starting Member

22 Posts

Posted - 2009-07-17 : 07:49:10

Is it possible to add new column values on existing rows in SQL server??

Like insert into ON EXISTING table1 (newcol) values('a')

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 07:52:36

Hi

I think update is possible...

-------------------------
R..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-17 : 07:59:32

alter table tblname add newcolname varchar(1) constraint constraint name default ('a') with values

sharankruthi
Starting Member

22 Posts

Posted - 2009-07-17 : 08:05:35

I need to insert some data based on the value i have in a row..
Just see this code
ALTER TABLE chennaiexp
DROP COLUMN status

SELECT * from chennaiexp
ALTER PROC getage
AS
DECLARE agec CURSOR FOR SELECT age FROM chennaiexp
OPEN agec
DECLARE @viewage tinyint
DECLARE @tktstatus varchar(20)
FETCH next FROM agec into @viewage
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF(@viewage < 10)
BEGIN
set @tktstatus = 'No ticket'
END
IF(@viewage BETWEEN 10 and 15)
BEGIN
set @tktstatus = 'Half ticket'
END
ELSE
set @tktstatus = 'Full ticket'
FETCH next FROM agec into @viewage
INSERT INTO ON EXISTING chennaiexp (status) VALUES (@tktstatus)
if(@@ERROR <> 0)
print 'error'
END
CLOSE agec
DEALLOCATE agec

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-17 : 08:57:05

add your new column

then something like...


update chennaiexp
set <new column name> = 
	case	when viewage < 10 then 'No ticket'
			when viewage > 9 and < 16 then 'half ticket'
			else 'Full ticket' end

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-17 : 08:57:50

If this does not work, follow the How to ask link in my signature, and supply some sample tables and data

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:16:01

quote:
Originally posted by sharankruthi

I need to insert some data based on the value i have in a row..
Just see this code
ALTER TABLE chennaiexp
DROP COLUMN status

SELECT * from chennaiexp
ALTER PROC getage
AS
DECLARE agec CURSOR FOR SELECT age FROM chennaiexp
OPEN agec
DECLARE @viewage tinyint
DECLARE @tktstatus varchar(20)
FETCH next FROM agec into @viewage
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF(@viewage < 10)
BEGIN
set @tktstatus = 'No ticket'
END
IF(@viewage BETWEEN 10 and 15)
BEGIN
set @tktstatus = 'Half ticket'
END
ELSE
set @tktstatus = 'Full ticket'
FETCH next FROM agec into @viewage
INSERT INTO ON EXISTING chennaiexp (status) VALUES (@tktstatus)
if(@@ERROR <> 0)
print 'error'
END
CLOSE agec
DEALLOCATE agec

i think what you need is this


UPDATE chennaiexp 
SET status=CASE WHEN age <10 THEN 'No ticket'
                WHEN age BETWEEN 10 and 15 THEN 'Half ticket'
                ELSE 'Full ticket'
           END

sharankruthi
Starting Member

22 Posts

Posted - 2009-07-20 : 00:07:25

Actually i need to do it using cursor..
I did it like
UPDATE chennaiexp
SET status = @tktstatus where current of agec

agec - cursor.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-07-20 : 07:40:24

I think you WANT to use a cursor. Set based is the correct way to do it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-24 : 13:15:03

quote:
Originally posted by sharankruthi

Actually i need to do it using cursor..
I did it like
UPDATE chennaiexp
SET status = @tktstatus where current of agec

agec - cursor.

was it an assignment for cursors?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources