title: From CURSOR to a SET based approach utilizing a temp table with no IDs?
body: <p>I currently have a <code>CURSOR</code> that loops through a temporary table that contains the paths to  hundreds of various .txt files that need to be <code>BULK INSERT</code> into a <code>Persons</code> table. The temporary table is also established from a single .txt file that has no identity columns. The FileList.txt text file looks like the following:</p>
  
  <pre><code>...
  E:\Dept1\Type1\2005.txt
  E:\Dept1\Type1\2006.txt
  E:\Dept1\Type1\2007.txt
  E:\Dept2\Type1\2005.txt
  E:\Dept2\Type1\2006.txt
  ...
  </code></pre>
  
  <p>I'm loading the FileList.txt into a temporary table with a <code>BULK INSERT</code>. Given this, the only column in the temporary table is the <code>Path</code> column. If I had an additional identity column, the <code>BULK INSERT</code> would fail due to there being as mismatch of column numbers.</p>
  
  <p>I want to fine tune this to use a <code>WHILE</code> loop. However, I've hit a bit of a dead brain on how best to utilize one. Every solution that uses a<code>WHILE</code> loop that I've seen assumes that a sequential identity column exists. My current query is as follows:</p>
  
  <pre><code>CREATE TABLE #NAMES_filelist (Path VARCHAR(MAX))
  BULK INSERT #NAMES_filelist FROM 'E:\FileList.txt' WITH 
      ( 
      ROWTERMINATOR = '\n'
      )
  DECLARE @FILEPATH VARCHAR(MAX)
  DECLARE @SQLBULK VARCHAR(MAX)
  -- Beginning the cursor to loop through the file list.
  DECLARE C1 CURSOR
  FOR SELECT Path FROM #NAMES_filelist
  OPEN C1
  FETCH NEXT FROM C1 INTO @FILEPATH
  WHILE
      @@FETCH_STATUS &lt;&gt; -1
      BEGIN
         -- Setting @SQLBULK to do the bulk insert of the index files.
         SET @SQLBULK = 
         -- Utilizing a view due to additional columns in dbo.Persons in comparison to the index file.
         'BULK INSERT Persons_view FROM ''' + @FILEPATH + ''' WITH 
            ( 
             MAXERRORS = 0
            ,FIELDTERMINATOR = ''|''
            ,ROWTERMINATOR = ''\n''
            )'
         EXEC (@SQLBULK)
         -- Updating the DEPT and INDEXFILE columns for lookup purposes.
         UPDATE
            Persons
         SET
            DEPT = REVERSE(SUBSTRING(REVERSE(@FILEPATH), CHARINDEX('\',REVERSE(@FILEPATH)) + 1, CHARINDEX('\',REVERSE(@FILEPATH), CHARINDEX('\',REVERSE(@FILEPATH)) + 1) - CHARINDEX('\',REVERSE(@FILEPATH)) - 1))
            ,INDEXFILE = REVERSE(LEFT(REVERSE(@FILEPATH),CHARINDEX('\', REVERSE(@FILEPATH), 1) - 1))
         WHERE
            DEPT IS NULL
            AND INDEXFILE IS NULL
      FETCH NEXT FROM C1 INTO @FILEPATH
      END
  CLOSE C1
  DEALLOCATE C1
  DROP TABLE #NAMES_filelist
  </code></pre>
  
  <p>Any fresh ideas you guys have would be incredibly useful.</p>