Sql Server - String concatenate

we construct a simple test to try it out. You've seen some of this code before! I did this with SQL Server 2005, on a very modest developmentsystem that I always us for writing time-critical code. It may me different for SQL Server 2008 with lots of memory

DECLARE @LotsOfText VARCHAR(MAX)
SELECT   @LotsOfText = BulkColumn
FROM     OPENROWSET(BULK 'C:\workbench\c_a_p.txt', SINGLE_BLOB) AS x  

/* read each word into a table (we make it a temporary table as we don't want to keep it for ever!*/
CREATE TABLE ##word
  
(
    
Word_ID INT IDENTITY(1, 1)
                
PRIMARY KEY,
    
item VARCHAR(80)
   )

INSERT   INTO ##word
        
(ITEM)
        
SELECT   item
        
FROM     wordchop(@LotsOfText)
---48 seconds (214243 words)

--USE DOCUMENTER
--SELECT NAME FROM SYSOBJECTS WHERE NAME LIKE 'NUMBERS'

--169683
CREATE TABLE ##log
  
(
    
Log_ID INT IDENTITY(1, 1),
    
Event VARCHAR(30),
    
param INT,
    
InsertionDate DATETIME DEFAULT GETDATE()
   )
DECLARE @command VARCHAR(255),
  
@ActualCommand VARCHAR(255)
SELECT   @Command = '
DECLARE @STRING VARCHAR(MAX)
insert into ##log (event,param) Select ''concatenate || rows'',||
SET ROWCOUNT ||
sELECT @sTRING =COALESCE(@String,'''')+item from ##word'
DECLARE @ii INT

SELECT  
@ii = 1000
WHILE @ii < 200000
  
BEGIN
      SELECT  
@ActualCommand = REPLACE(@command, '||', CONVERT(VARCHAR(8), @ii))
      
EXECUTE (@ActualCommand)
      
SELECT   @ii = @ii + 1000
  
END

SELECT  
event,
      
[rows] = param,
      
[Time (Ms)] = DATEDIFF(ms, [InsertionDate],
                           (
SELECT [InsertionDate]
                              
FROM ##log f
                              
WHERE f . Log_ID = g . Log_ID + 1
                          
)
                       )
FROM     ##log g
WHERE    Log_ID < ( SELECT MAX (Log_ID) FROM ##log )

By C_A P   Popularity  (2362 Views)