Text file searching using SQL
Although the approach is 'peculiar in choice of tools', doing what you do should work just fine. The only part that I would definitely want to change is the loading of the results. Currently you are BULK INSERTing a file into a temp-table, then copy all that data into a table-variable, followed by deleting everything from the freshly (and the old!) copied data where it does not match a given criteria.

I would propose the following changes:


do not use Table-variables for this. They're fine for (very) small data-sets that don't require a lot of manipulation but will confuse the Query Optimizer when you do lots of inserts and deletes on them
do not use the TEXT datatype, rather use varchar(max)
use IDENITITY instead of RowNumber(). RownNumber works great, but it requires sorting the data. Also be very careful with this: doing RowNumber() ordered over a 'constant' might return different results than you originally anticipated and most certainly does not guarantee that you'll get things in the order they were originally in the file !!!!!
variable naming: @filename is much more readable than @ID


Doing some cleaning I ended up with below which in my tests on a couple of 100 small files ran slightly faster than the original. There might be a nicer way than the tempdb..stagingTable approach which will cause issues when the script is run in parallel, but I didn't see an easier way out to be able to use LineNum as an identity which in my opinion is must for what you're trying to achieve!

--Drop temp table if it already exists
IF OBJECT_ID('tempdb..tempdb..stagingTable') IS NOT NULL
    DROP TABLE tempdb..stagingTable

IF OBJECT_ID('tempdb..#files') IS NOT NULL
    DROP TABLE #files

IF OBJECT_ID('tempdb..#foundText') IS NOT NULL
    DROP TABLE #foundText

----------------------
----------------------
GO
--Crete temp table to store output lines from text file
CREATE TABLE tempdb..stagingTable
(
    LineNum int IDENTITY(1, 1) PRIMARY KEY,
    LineText varchar(max),
)

GO
-- since we can't tell BULK-insert to skip the LineNum column, we create a view without it
CREATE VIEW vtempFileContents AS SELECT LineText FROM tempdb..stagingTable
GO

--Create declared table to store found file names in the directory
CREATE TABLE #files
(
    filename VARCHAR(255)
    ,depth INTEGER
    ,files BIT
)

--Create declared table to store the text the string ws found in along with the filpath of the file.
CREATE TABLE #foundText
(
    LineNum int
    ,foundText TEXT
    ,filename VARCHAR(255),
    PRIMARY KEY (filename, LineNum)
)

GO

DECLARE @FindText AS VARCHAR(255)
DECLARE @FileDir AS VARCHAR(255)

--Directory to search
SET @FileDir = 'C:\temp\test\'
--Text to search
SET @FindText = 'common'

--Create table of files in the directory
INSERT INTO #files 
EXEC xp_dirtree @FileDir, 10, 1

--Varchar used to store the derived SQL string
DECLARE @sql NVARCHAR(1000);

--Create cursor for file searching
DECLARE @text VARCHAR(255)
DECLARE @filename VARCHAR(255)

DECLARE files_loop CURSOR LOCAL FAST_FORWARD FOR SELECT @FileDir + filename FROM #files

--Open cursor
OPEN files_loop
FETCH NEXT FROM files_loop into @filename
WHILE @@FETCH_STATUS = 0
BEGIN
    --Truncate temp table prior to searching a file
    TRUNCATE TABLE tempdb..stagingTable
    SET @sql = 'BULK INSERT vtempFileContents FROM "' + @filename + '"'
    EXEC(@sql);

    --Insert matching records in results table
    INSERT INTO #foundText (LineNum, foundText, filename)
    SELECT LineNum, LineText, @filename 
      FROM tempdb..stagingTable
     WHERE LineText IS NOT NULL
       AND LineText LIKE '%' + @FindText + '%'

    FETCH NEXT FROM files_loop into @filename
END

--Close off and dispose of unneeded resources
CLOSE files_loop
DEALLOCATE files_loop

--Display results
SELECT * FROM #foundText

GO
-- cleanup
DROP VIEW vtempFileContents
DROP TABLE tempdb..stagingTable
DROP TABLE #foundText
DROP TABLE #files