June 15, 2012 at 2:40 pm
Hi, I am looking for a higher performance way to take the text of a series of rows,
concatenate it, and store it in a different table. The source data is from a vendor
application, so I can not change the data source. The data is a notes table, which
has a unique note id, along with a sequence number. Each note is only allowed to be
75 characters long. However, when we use this data it is needed to be a single
note entry with the entire note in a single record. It is a larger table,
around one million records. Presently I can get the results I need, with using a
quirky update function. However, that is actually very slow, since it would run
for each source row. It presently takes around 45 minutes to complete. I would
think there must be a better way to do this. Below is TSql to generate a table
and sample data. The result I need to get to would be a table with 2 columns,
the NoteId and the complete note text. Something like this:
NoteID : 'Note Id AA 1'
NoteText : 'Note AA1, Seq 1 Text: Varies in length Note AA1, Seq 2 Text: Varies in length Note AA1, Seq 3 Text: Varies in length ' ...to... ' Note AA1, Seq 500 Text: Varies in length'
Anyone know a clever alternative I am not aware of?
Data SQL
Create Table [#NotesSource] ([NoteId] [varchar] (22) NOT NULL,
[NoteSeq] [Int] NOT Null,
[NoteText] [varchar] (75) NOT NULL)
INSERT INTO [#NotesSource] (NoteId, NoteSeq, [NoteText])
Select
'Note Id AA ' + Cast(NId.Nbr as varchar) As NoteId,
SEQ.Nbr As NoteSeq,
'Note AA' + CAST(NId.Nbr AS varchar)+ ', Seq ' + Cast(SEQ.Nbr as varchar) + ' Text: Varies in length' As NoteText
From (Select ROW_NUMBER() OVER(ORDER BY COLUMN_NAME DESC) AS 'Nbr'
From INFORMATION_SCHEMA.COLUMNS) NId
Cross Join (Select ROW_NUMBER() OVER(ORDER BY COLUMN_NAME DESC) AS 'Nbr'
From INFORMATION_SCHEMA.COLUMNS) Seq
Where NId.Nbr Between 1 and 2000 AND
SEQ.Nbr Between 1 and 500
Thanks!
June 15, 2012 at 3:00 pm
Hopefully this helps you move in the right direction.
WITH CTE AS
(
SELECT DISTINCT
NoteId,
object_id
FROM dbo.NotesSource
)
SELECT NoteId,
Notes = STUFF((
SELECT ',' + ns.NoteText
FROM dbo.NotesSource ns
WHERE ns.NoteId = c.NoteId
ORDER BY ns.NoteSeq
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE c
ORDER BY NoteId;
June 15, 2012 at 3:03 pm
Ahh, I keep forgetting the For XML path idea. I will have to play with that until I understand it.
Thanks!
June 15, 2012 at 3:15 pm
The code I provided creates a csv list. You'll have to modify it to concatenate your notes into a single field. Perhaps using a blank (space) instead of a comma.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply