Inserting records and appending additonal items to the new record

• myschif

  Say Hey Kid

  Points: 695

  More actions

  January 18, 2005 at 10:23 am

  #65510

  Hello,

  I have a table that holds notes for service calls. It is a one (call) to many (notes) arrangement. I need to insert these notes into a table that has a one(call) to one(note) relationship.

  I can create the insert statement to insert notes but I am unclear how to append the many (notes) to the new single note field?

  Any ideas would be appreciated.

  
  
  "Some like to understand what they believe in. Others like to believe in what they understand."
  --Stanislaus J. Lec

• Charles Otten

  Default port

  Points: 1475

  More actions

  January 18, 2005 at 11:29 am

  #537903

  I think you'll need to use a cursor to do it.  yuck.

  declare curCallNotes cursor static for select call, note from tCalls open curCallNotes

  declare @Call int --This would be whatever the key value is

  declare @Note varchar(XXX) --This is whatever the old note field was

  declare @LastCall int --Again whatever the call identifier is

  set @lastcall = 0

  fetch next from curCallNotes into @Call, @Note

  while @@fetch_status <> -1

  begin

    if @lastcall <> @call

    begin

      insert into tNewCallNotes

      (call, note)

      values

      (@call, @note)

    end

    else

    begin

      --chr(13) will place a carriage return between notes

      update tNewCallNotes set note = note + chr(13) + @note

    end

    set @lastcall = @call

    fetch next from curCallNotes into @Call, @Note

  end

   

  close curcallnotes

  deallocate curcallnotes

   

   

  Something like this should do the trick

  If the phone doesn't ring...It's me.

• vadba

  SSChampion

  Points: 11133

  More actions

  January 19, 2005 at 7:39 am

  #538073

  Another quick and dirty approach that uses a temporary table instead of a cursor:

  --DROP TABLE letters

  GO

  CREATE TABLE letters

  (

    ref int,

    note varchar(1000)

  )

  SET NOCOUNT ON

  INSERT letters (ref, note) VALUES (1, 'Ref 1 Note A')

  INSERT letters (ref, note) VALUES (1, 'Ref 1 Note B')

  INSERT letters (ref, note) VALUES (2, 'Ref 2 Note A')

  INSERT letters (ref, note) VALUES (3, 'Ref 3 Note A')

  INSERT letters (ref, note) VALUES (4, 'Ref 4 Note A')

  INSERT letters (ref, note) VALUES (4, 'Ref 4 Note B')

  INSERT letters (ref, note) VALUES (4, 'Ref 4 Note C')

  INSERT letters (ref, note) VALUES (4, 'Ref 4 Note D')

  INSERT letters (ref, note) VALUES (5, 'Ref 5 Note A')

  INSERT letters (ref, note) VALUES (5, 'Ref 5 Note B')

  INSERT letters (ref, note) VALUES (6, 'Ref 6 Note A')

  SET NOCOUNT OFF

  PRINT 'STARTING DATA:'

  PRINT ''

  SELECT ref, note FROM letters ORDER BY ref

  SET NOCOUNT ON

  DECLARE @ref int, @notes varchar(8000), @delim varchar(2)

  --SET @delim = Char(13)

  SET @delim = ' '

  CREATE TABLE #temp

  (

    ref int,

    notes varchar(8000)

  )

  INSERT #temp (ref) SELECT DISTINCT ref FROM letters

  SELECT @ref = Min(ref) FROM #temp WHERE notes IS NULL

  WHILE @ref IS NOT NULL

  BEGIN

    SET @notes = ''

    UPDATE letters

      SET @notes = @notes + note + @delim

     WHERE ref = @ref

       AND note IS NOT NULL

    UPDATE #temp

       SET notes = @notes

     WHERE ref = @ref

    SELECT @ref = Min(ref) FROM #temp WHERE notes IS NULL

  END

  SET NOCOUNT OFF

  PRINT ''

  PRINT ''

  PRINT 'RESULTS:'

  PRINT ''

   

  SELECT ref, notes

    FROM #temp

   ORDER BY ref

  DROP TABLE #temp

   

• myschif

  Say Hey Kid

  Points: 695

  More actions

  January 19, 2005 at 5:57 pm

  #538201

  I will try these.

   

  Thank you

  
  
  "Some like to understand what they believe in. Others like to believe in what they understand."
  --Stanislaus J. Lec