Problems with Adding text

[Azzuron]

We are trying to translate some raw flat data into a cart. What we have done is BCP the raw files into the database, and then we begin to run a series of SQL commands to copy the data and format it for the cart. This ultimatly takes 3 different flat file databases and merges them all into one. The current problem is with the following command:

update [translation-new].dbo.products 
set 
    tp.thumbnail = 'graphics/00000001/thumbs/'+td.ProdImage+'.jpg', 
    tp.image = 'graphics/00000001/'+td.ProdImage+'.jpg',
    tp.descrip = td.ProdDescription+'<BR><BR>'+td.MarketingDescription
from
    [translation-new].dbo.products as tp, datapaq.dbo.cnetprodinfo as td
where tp.TDProdID = td.ProdID;

When the comand executes, i get:

Msg 402, Level 16, State 1, Line 37
The data types text and varchar are incompatible in the add operator.

I find this very strange, as i used this type of syntax in a different query.

insert into 
    [translation-new].dbo.comersusproducts 
        (suppliername, sku, description, details, smallimageurl, imageurl, price, listprice, cost, weight,
         length, height, width, recordsource)
select
    datapaq.dbo.datapaq.manufacturername, datapaq.dbo.datapaq.manufacturerprodid, datapaq.dbo.datapaq.proddescription,
    datapaq.dbo.datapaq.proddescription, 'graphics/00000001/thumbs/'+datapaq.dbo.cnetprodinfo.prodimage+'.jpg',
    'graphics/00000001/'+datapaq.dbo.cnetprodinfo.prodimage+'.jpg', datapaq.dbo.datapaq.msrp, datapaq.dbo.datapaq.msrp,
    datapaq.dbo.price.prodprice, datapaq.dbo.datapaq.prodweight, datapaq.dbo.datapaq.prodlength,
    datapaq.dbo.datapaq.prodheight, datapaq.dbo.datapaq.prodwidth, 'TD'
from
    datapaq.dbo.cnetprodinfo, datapaq.dbo.datapaq, datapaq.dbo.price
where
        datapaq.dbo.cnetprodinfo.prodid = datapaq.dbo.datapaq.prodid 
    AND 
        datapaq.dbo.cnetprodinfo.prodid = datapaq.dbo.price.prodid;

The difference between these two, is that the one is a select and the other is an update. So, trying to be crafty id did the following:

update tp 
set 
    tp.thumbnail = (select 'graphics/00000001/'+ProdImage+'.jpg' from datapaq.dbo.cnetprodinfo where prodid = td.prodid),
    tp.image = (select 'graphics/00000001/thumbs'+ProdImage+'.jpg' from datapaq.dbo.cnetprodinfo where prodid = td.prodid),
    tp.descrip = (select proddescription+'<br><br>'+marketingdescription from datapaq.dbo.cnetprodinfo where prodid = td.prodid)
from
    [translation-new].dbo.products as tp, datapaq.dbo.cnetprodinfo as td
where tp.TDProdID = td.ProdID;

At first i only did the thumbnail field. removing the others, and i ran the query. It worked. Great! I added the other two fields and it failed due to the same error again. What is goin on?! So i broke it down more, and removed the last line dealing with marketing descriptions... worked again. so its this last line.

The marketingdescription and proddescription, are text types. i dont think ill be able to make them varchar as some of these descriptions are pretty big. Also, the field i am copying these to, is a text type. so it seems to be a limitation of the + operator on the datatype. So i guess the question in short is....

How do you merge "textfield1" & 'text string' & "textfield2", where textfield is a field that is of type "text". For example if the fields above pull from the database:

textfield1 = 'This is product description 1'
textfield2 = 'This is product description 2'
the value updated in the cart table, for the [full product description] is:
"This is product description 1text stringThis is product description 2"

Thanks

--Dave

[Azzuron]

I am just posting to ask if the question i posed is clear. I know it can take some time to get a reply, and that i may not get one at all, but i dont want my posting to be overlooked, as this is pretty much a show stopper for us at the moment.

the basic of my question is:

I have three tables, they have 1 field that is type 'Text'

I am trying to take the text field from 2 of the tables and combine them to make one field to put into the 3rd table.

--Dave

[ymk]

I am just posting to ask if the question i posed is clear. I know it can take some time to get a reply, and that i may not get one at all, but i dont want my posting to be overlooked, as this is pretty much a show stopper for us at the moment.

the basic of my question is:

I have three tables, they have 1 field that is type 'Text'

I am trying to take the text field from 2 of the tables and combine them to make one field to put into the 3rd table.

--Dave

I don't think SQL allows to concatenate two fields of Datatype 'text'.

You might need to use either Cast or Convert functions to acheive it.

For Ex:
A -- DataType 'text'
B -- DataType 'text'
Inserting into another column C

Update TableA
Set C = Convert(VarChar(80),A) + Convert(VarChar(80),B)