SQLTeam.com | Weblogs | Forums

Inserting Data

sql2012

#1

Hi All,
I'm trying to copy a request from one table to another Table and concatenate a standard text to certain fields just for easy identification on the front end. Concatenation is fine but year over year I'm concatenating the same text over and over again

How do I make sure that I only concatenate the standard text if it is not already part of a field.Below is my query

INSERT INTO [CustomerInfo].[dbo].[RequestDataDraft]
([reqno]
,[submitdate]
,[duedate]
,[title]
,[comments])

SELECT @newrequestid
,getdate()
,[duedate]
,[title] + ' ' +'Renewal Request'
,[comments]
FROM [CustomerInfo].[dbo].[RequestData]
where reqno = @oldreqno

the Renewal Request is the standard text I concatenate and it is getting duplicated. How do I only concatenate if it does not exist first hand

Thank You


#2

...
,CASE WHEN [title] LIKE '% Renewal Request%' THEN [title] ELSE [title] + ' ' +'Renewal Request' END
...


#3

I would worry that the "Renewal Request" text, appended to the [title], got mangled somehow - e.g. an operator edited it. I suppose if that happened the next update would put it back again ...

... but I tend to handle this sort of "presentation" type issue by appending the + ' ' +'Renewal Request' when the display is done - sometimes by having a VIEW that has an EXISTS to the child table (i.e. the Source which defines that there is a Renewal Request) and then conditionally appending the "Renewal Request" text to the [title]


#4

For consistency and efficiency, you really ought to set a bit flag in the row when the "Renewal Request" text is appended, then subsequently you can just check that flag to determine whether to concat the text or not.