Replacing existing records in a table

I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?updates?

The message I have entered is too short.|||can UPDATE do multiple column update?

actually the purpose is for rerun of query. If i were to use UPDATE, i need to check if the record exist first. That's not efficient. I am looking for direct replacement.|||can UPDATE do multiple column update?

yes

actually the purpose is for rerun of query. If i were to use UPDATE, i need to check if the record exist first.

maybe. maybe not.

That's not efficient. I am looking for direct replacement.

really? that is all in how you skin the cat.

why don't you read Brett Kaiser's sticky at the top of this board, follow the directions, and save everyone a lot of time.

Replacing characters in a text field

I have a large table, tblMessage, which stores e-mail messages in text
fields. I need to remove the carriage returns the data in these fields,
but I have not yet figured out how to do so.

I thought that the way to do this would be with the REPLACE function;
unfortunately, of course, the REPLACE function cannot work with TEXT
fields. I tried CASTing the text field to VARCHAR(8000); however, some
of the rows have more than 8000 characters in the text field, so it bombs.

Here is the SQL that I tried:

select
msgID,
msgSent,
msgFromType,
msgFromID,
msgSubject,
REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
newMessage,
msgOriginal,
attID
into tblMessageNew
from tblMessage

I'm at my wit's end. Truncating the text field to 8000 character is an
acceptable option, but I can't even seem to be able to do that.

I'm using SQL Server version 7.Richard S. Crawford (rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com) writes:
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.

Bombs with what? It's always helpful if you include the error message.

I was able to run this on SQL Server 7:

create table hh (a text not null)
go
declare @.d varchar(8000), @.df varchar(8000)
select @.d = replicate('Why are you here? You should be there!', 8000/30)
select @.df = replicate('Why are you here? You should be there!', 8000/30)
insert hh (a)
exec ('select ''' + @.d + @.df + '''')
select datalength(a) from hh
select replace(cast(a as varchar(8000)), 'Why', 'Porque') from hh
go
drop table hh

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Taking the CR/LF out before inserting the text would avoid your problem!!!

The only way I can think of doing this is to chunk the text field into 8000
character as in
http://tinyurl.com/3fqxv

One way of doing this:
http://tinyurl.com/236hf

John

"Richard S. Crawford" <rscrawfordDUCK@.mossREMOVEWATERFOWLroot.com> wrote in
message news:ca4rog$d8b$1@.woodrow.ucdavis.edu...
> I have a large table, tblMessage, which stores e-mail messages in text
> fields. I need to remove the carriage returns the data in these fields,
> but I have not yet figured out how to do so.
> I thought that the way to do this would be with the REPLACE function;
> unfortunately, of course, the REPLACE function cannot work with TEXT
> fields. I tried CASTing the text field to VARCHAR(8000); however, some
> of the rows have more than 8000 characters in the text field, so it bombs.
> Here is the SQL that I tried:
> select
> msgID,
> msgSent,
> msgFromType,
> msgFromID,
> msgSubject,
> REPLACE (CAST(msgMessage AS varchar(8000)), CHAR(13), '<BR>') AS
> newMessage,
> msgOriginal,
> attID
> into tblMessageNew
> from tblMessage
> I'm at my wit's end. Truncating the text field to 8000 character is an
> acceptable option, but I can't even seem to be able to do that.
> I'm using SQL Server version 7.