Copy data from one column to another

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 2005 Forums

Transact-SQL (2005)

Copy data from one column to another

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-10-21 : 03:15:36

Hi

I would like to copy data from column and append to data in another column in the same table. Also to include a comma (,) while appending it. See examples below
FROM THIS


UserIndex	Address1	Suburb
1		
99	Plot 51465	        Gaborone
1985	c-9, Louve Villa	Block 6
2262	Plot 26095,Block9	Gaborone

TO THIS


UserIndex	Address1	        Suburb
1		
99	Plot 51465,Gaborone	        Gaborone
1985	c-9, Louve Villa, Block 6	Block 6
2262	Plot 26095,Block9, Gaborone	Gaborone

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-21 : 04:11:20

Update your_table
set Address1=Address1+','+ Suburb

Madhivanan

Failing to plan is Planning to fail

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-10-21 : 04:17:29

Thank you so much

gaauspawcscwcj
Starting Member

29 Posts

Posted - 2009-10-21 : 04:26:27

u can try this
--------------------------------------------------
begin
declare @A nchar(50), @B nchar(50), @C nchar(50)
declare test_cursor CURSOR
FOR
select
a,
b ,
c
from
test
-- update data
open test_cursor
fetch next from test_cursor
into @A,@B,@C

while @@fetch_status = 0
begin
update test set b = LTRIM(RTRIM(@B)) + ',' + LTRIM(RTRIM(@C)) where a = @A
-- next record
fetch next from test_cursor
into @A,@B,@C
end
close test_cursor
DEALLOCATE test_cursor
end
---------------
my table have 3 column A,B,C varchar(50)

gaauspawcscwcj

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-10-21 : 04:29:04

quote:
Originally posted by madhivanan

Update your_table
set Address1=Address1+','+ Suburb

Madhivanan

Failing to plan is Planning to fail

After running your above statment I got an error. Please see below

String or binary data would be truncated.
The statement has been terminated.

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-10-21 : 05:01:59

It is OK I managed to solve it by increasing the length of the column

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-21 : 05:03:05

quote:
Originally posted by gaauspawcscwcj

u can try this
--------------------------------------------------
begin
declare @A nchar(50), @B nchar(50), @C nchar(50)
declare test_cursor CURSOR
FOR
select
a,
b ,
c
from
test
-- update data
open test_cursor
fetch next from test_cursor
into @A,@B,@C

while @@fetch_status = 0
begin
update test set b = LTRIM(RTRIM(@B)) + ',' + LTRIM(RTRIM(@C)) where a = @A
-- next record
fetch next from test_cursor
into @A,@B,@C
end
close test_cursor
DEALLOCATE test_cursor
end
---------------
my table have 3 column A,B,C varchar(50)

gaauspawcscwcj

Why do you want to use a cursor?
What is wrong with my suggestion?

Madhivanan

Failing to plan is Planning to fail

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-10-21 : 05:36:20

madhivanan's statement was very short and it worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources