SQL Server Performance

CONCATENATE(???) aggregate function in MS SQL

Discussion in 'T-SQL Performance Tuning for Developers' started by bolo, Feb 26, 2006.

  1. bolo New Member

    Hi

    I have one table(Table1) that looks like this:
    ---------------------------------------------
    ID(int), Comment(varchar(250))
    1, abc
    2, efg
    3, wer
    1, sqs
    2, bcd
    ---------------------------------------------

    I want to create a table(Table2) where I have ID as PrimaryKey and Comments concatenated:
    --------------------------------------------
    ID(int)PK, Comments(varchar(8000))
    1, abcsqs
    2, efgbcd
    3, wer
    ---------------------------------------------



    Is there a CONCATENATE(???) aggregate function in MS SQL? I use the following procedure to create my second Table.
    First I created a function:
    -----------------------------------------------------------------
    CREATE FUNCTION [dbo].[fn_LongComment] (@ID int)
    RETURNS varchar(8000)
    AS
    BEGIN

    DECLARE @LongComment varchar(8000)


    DECLARE MyCursor CURSOR FAST_FORWARD FOR SELECT Comment FROM Table1 WHERE ID = @ID ORDER BY ID
    OPEN MyCursor
    FETCH NEXT FROM MyCursor INTO @Comment
    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @LongComment = @LongComment + @Comment

    FETCH NEXT FROM MyCursor INTO @Comment
    END
    CLOSE MyCursor
    DEALLOCATE MyCursor

    RETURN (@LongComment)

    END
    ---------------------------------------------------------------------


    Then on Table2 I insert all unique IDs.
    Next I run the statement below:
    ---------------------------------------------------------------------
    UPDATE Table2 SET Comment = dbo.fn_LongComment(ID)
    ---------------------------------------------------------------------

    Table1 is about 5 mil records and the UPDATE statement takes forever. Is there a better way of doing this?
    Also, what's next after [varchar(8000)] in terms of size? is [text] the right choice. is it OK to go with [text]?
    I think that some ID's will have very long comments.

    Thank you,


    bolo
  2. ranjitjain New Member

    check this logic:


    declare @str varchar(50)
    set @str=''

    select @str=@str+comments from table1
    where ids=1

    select @str
  3. bolo New Member

    Thanks ranjitjain,

    Ho do I write my UPDATE statement? A loop should be generated and each time @str = '', right?
  4. Madhivanan Moderator

  5. bolo New Member

    Hi Madhivanan,

    Thanks for the link. Very helpful.


    bolo

Share This Page