title: Efficient query to split a delimited column into separate rows in another table
body: <p>I have some data that includes a column with delimited data. There are multiple records in the same column essentially:</p>
  <pre><code>A0434168.A2367943.A18456972.A0135374.A0080362.A0084546.A0100991.A0064071.A0100858
  </code></pre>
  <p>I'm trying to split up the data in this column into separate rows in another table. The values are of variable length, and delimited by periods. I've been attempting to create a lookup table for this data, using a cursor. Due to the volume of data, the cursor is unreasonably slow.</p>
  <p>My cursor looks like the following:</p>
  <pre><code>DECLARE @ptr nvarchar(160)
  DECLARE @aui nvarchar(15)
  DECLARE @getmrhier3 CURSOR 
  
  SET @getmrhier3 = CURSOR FOR
      SELECT  cast(ptr as nvarchar(160)),aui
      FROM    mrhier3
      FORWARD_ONLY
  OPEN @getmrhier3
  FETCH NEXT
      FROM @getmrhier3 INTO @ptr, @aui
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
      if(len(@ptr) &gt; 0)
      begin
          if(charindex('.',@ptr) &gt; 0)
          begin
              insert into mrhierlookup(hieraui,aui)
              values      (substring(@ptr,0,charindex('.',@ptr)),@aui)
              
              update  mrhier3
              set     ptr = substring(@ptr,charindex('.',@ptr)+1,LEN(@ptr))
              where   aui = @aui 
                and   ptr = @ptr
          end
          else
          begin
              insert into mrhierlookup(hieraui,aui)
              values      (@ptr,@aui)
              
              update  mrhier3
              set     ptr = ''
              where   aui = @aui 
                and   ptr = @ptr
          end
      end
      FETCH NEXT
          FROM @getmrhier3 INTO @ptr, @aui
  END
  
  CLOSE       @getmrhier3
  DEALLOCATE  @getmrhier3
  </code></pre>
  <p>The current version of the cursor just works on the leading value of the column. All lengths are arbitrary. The column is at most ~150 characters long.</p>
  <p>With the current dataset, building the lookup table will likely take days. It will have several million records.</p>
  <p>Is there a better way to efficiently (quickly) parse out this data into a separate table for the purpose of performing join operations more quickly?</p>