If "everything is back-end", why do you need to concatenate the personnelnumbers at all?
E 12°55'05.25" N 56°04'39.16"
OP wants to see the result in Query Analyser
Madhivanan
Failing to plan is Planning to fail
Well the problem in your case is the number of string concats that take place when doing this. So the solution is to reduce the number of concatenation operations by using a temp table.
I know this is a big ugly monster and normally I wouldn't recommend it to anyone - but for what you need (just seeing the stuff in QA) this will work a lot faster; I was able to run it in about 6 1/2 minutes versus your 20+ doing it without the cursor and temp table on a Core 2 Duo Laptop with 2 GB of RAM).
CREATE TABLE #TempColTable (ConVal nvarchar(max))
DECLARE collation_csr CURSOR FAST_FORWARD READ_ONLY FOR SELECT [Value] FROM TestTable
OPEN collation_csr
DECLARE @insertSection as nvarchar(max) DECLARE @numberPlaceHolder as nvarchar(10) DECLARE @rowCounter as bigint;
FETCH NEXT FROM collation_csr INTO @numberPlaceHolder;
SET @insertSection = @numberPlaceHolder + ', '; SET @rowCounter = 0;
WHILE(@@FETCH_STATUS = 0) BEGIN
FETCH NEXT FROM collation_csr INTO @numberPlaceHolder;
SET @insertSection = @insertSection + @numberPlaceHolder + ', ';
IF(@rowCounter = 7500) BEGIN INSERT INTO #TempColTable (ConVal) VALUES (@insertSection); SET @insertSection = ''; SET @rowCounter = 0; --reset the counter so we can insert this batch and move on END ELSE BEGIN SET @rowCounter = @rowCounter + 1; END END
CLOSE collation_csr DEALLOCATE collation_csr
DECLARE @insertedRecords as bigint; DECLARE @number varchar(max)
SELECT @insertedRecords = COUNT(ConVal) FROM #TempColTable
IF(@insertedRecords = 0) BEGIN SET @number = @insertSection; END ELSE BEGIN SELECT @number = COALESCE(@number + ', ', '') + [ConVal] FROM #TempColTable END
CAVEAT: This technique fails for me when the total number of characters in the variable is more than 255 (even when the variable is declared as varchar(8000)).
The final SELECT statement only shows the first 255 characters. I am using SQL Server (2000). Is that an issue?
You only get to SEE the first 255 characters in Query Analyzer, because that is the default setting. You can change this number of characters in Tools -> Option menu.
I know it is prehistoric thread - but you don't need to use variables or coalesce. You can do it all inline. See below.
declare @table table (
result_number int
)
insert into @table
select 12345 union
select 23456 union
select 34567 union
select 45678 union
select 56789 union
select 67890
select stuff(convert(varchar(max),(
select ','+ cast(result_number as nvarchar(255))
from @table b
FOR XML PATH('')
)),1,1,'')
Article: Using COALESCE to Build Comma-Delimited String
Posted - 10/02/2007 : 19:21:58
- but for what you need (just seeing the stuff in QA) this will work a lot faster; I was able to run it in about 6 1/2 minutes versus your 20+ doing it without the cursor and temp table on a Core 2 Duo Laptop with 2 GB of RAM).