Concatenate string in INSERT. Don't know # of records.

[uerobertson]

I need to do an INSERT where I SELECT, concatenate and INSERT all in one statement.

I've researched thread183-90896, and FAQ183-1067 but neither are exactly what I need. Would someone please be able to help me with the syntax?

This code works for one record at a time but uses too many statments:

CREATE PROCEDURE dbo.insertProductDescriptions 
			(@inaProdID AS int, 
			@locale AS char(2),
			@vendorVPN AS varchar(20))
AS	

DECLARE @String varchar(8000)

SELECT @String = ''

SELECT @String = @String + ' ' + CAST([Model Text] AS varchar(255)) 
FROM INAAdmin.AISTXD04
WHERE [Code of Text] = 'MTD' AND [IIC Model-Number] = @vendorVPN

UPDATE prodDescTest 
	SET tech_spec = 
		CAST (@String AS text)
	WHERE prodDescTest.ina_prod_id = @inaProdID 
	AND prodDescTest.locale = @locale

It produces this correct output:

ina_prod_id:  278 
locale:       en
prod_desc:    Hooded Sweatshirt
tech_spec:    Kangaroo pocket;new etirel logo embroidery on left chest inside brushed 280gr/m2

This code does not work:

CREATE PROCEDURE dbo.insertProductDescriptions 
		(@locale AS char(2))
	
AS

DECLARE @String varchar(8000)

SET @String = ''

INSERT INTO prodDescTest
SELECT DISTINCT p.ina_prod_id, 
	@locale, 
	a5.[Product Short Desc]+' '+ a5.[Product Short Desc2] AS prod_desc,
	@String + ' ' + CAST(a4.[Model Text] AS varchar(255)) AS tech_spec
FROM product_style p 
LEFT JOIN INAAdmin.AISMBD05 a5
ON p.vpn = CAST(LTRIM(a5.[IIC Model-Number]) AS varchar(20)) 
LEFT JOIN INAAdmin.AISTXD04 a4
ON p.vpn = CAST(LTRIM(a4.[IIC Model-Number]) AS varchar(20)) 
AND LTRIM(RTRIM(a4.[Code of Text])) LIKE 'MTD'

It produces this incorrect output:

ina_prod_id:   278 
locale:        en
prod_desc:     Hooded Sweatshirt
tech_spec:     Kangaroo pocket;

ina_prod_id:   278 
locale:        en
prod_desc:     Hooded Sweatshirt
tech_spec:     new etirel logo embroidery on left chest

ina_prod_id:   278 
locale:        en
prod_desc:     Hooded Sweatshirt
tech_spec:     inside brushed 280gr/m2

There are a few thousand records (unique ina_prod_ids) and from 0 and up tech_spec records that need to be concatenated.

I understand that the reason the second snippet of code isn't working is because the @String isn't concatenating to itself because the

SELECT @String = @String...

is missing. So, how can I add it in? (Please don't say it's impossible).

Thanks,
Ursula
Win 2000 and SS 2000

 

[SamGarland]

AFAIK you can't mix assignment with data retrieval in the one select statement.

How about iterating over the result set of the select with a cursor, and performing a string concatenation and a row insertion each time around the loop?

Sam

 

[uerobertson]

Thanks but I would rather avoid cursors. I'm actually rewriting code that used cursors (and other expensive code). I'm nearly done (this procedure and one other to complete) and the processing time has gone down from over 4 hours to under 3 minutes.

I know I can do part of the INSERT and then do an UPDATE for the tech_spec stuff but I was hoping to do it all in one statement.

Thanks,
Ursula

 

[SamGarland]

Have a look at

http://www.sqlteam.com/item.asp?ItemID=3856

It shows 3 different approaches to this class of problem and the performance of each approach with test data.

Try these styles with your own db and see which works best for you. In their case they found that cursor solution was the fastest.

 

[uerobertson]

Thanks SamGarland. I will have a look.

Ursula

 

[uerobertson]

SamGarland,
The page wasn't exactly what I needed but I did find the following article on the SQLTeam site that answered my question. Thanks for the link.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9978

Ursula