Text file searching using SQL

Question

I have the need to search a directory containing 1000+ files on remote servers where I only have access to SQL Server Management Studio and Explorer. I have written the below SQL statement which does what I need, only incredibly slowly. I fear using this many temp tables and the cursor is the main issue. Any ideas on how I can speed this up?

EDIT: I forgot to mention that I need to keep this query based. I will not necessarily have full permissions regarding jobs and/or SSIS packages.

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

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

--Directory to search
SET @FileDir = 'C:\Users\*********\Desktop\TEMP\'
--Text to search
SET @FindText = '1234'
----------------------
----------------------

--Crete temp table to store output lines from text file
CREATE TABLE #tempFileContents
(
    lineText TEXT
)

--Create declared table to store found file names in the directory
DECLARE @files table
(
    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.
DECLARE @foundText table
(
    LineNum INT
    ,foundText TEXT
    ,filename VARCHAR(255)
)

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

--Update file names to include filepath
UPDATE @files SET filename = @FileDir + filename

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

--Create cursor for file searching
DECLARE @text VARCHAR(255)
DECLARE @ID VARCHAR(255)
DECLARE IDs CURSOR LOCAL FOR select filename from @files

--Open cursor
OPEN IDs
FETCH NEXT FROM IDs into @ID
WHILE @@FETCH_STATUS = 0
BEGIN
    --Truncate temp table prior to searching a file
    TRUNCATE TABLE #tempFileContents
    SET @sql = 'BULK INSERT #tempFileContents FROM "' + @ID + '"'
    EXEC(@sql);

    --Insert into results table
    INSERT INTO @foundText
    (LineNum, foundText, filename)
    SELECT ROW_NUMBER() OVER( ORDER BY @ID ) AS 'rownumber', lineText, @ID FROM #tempFileContents

    DELETE FROM @foundText
    WHERE foundText NOT LIKE '%' + @FindText + '%'
    OR foundText IS NULL

    FETCH NEXT FROM IDs into @ID
END

--Close off and dispose of unneeded resources
CLOSE IDs
DEALLOCATE IDs
DROP TABLE #tempFileContents

--Display results
SELECT * FROM @foundText

Answer 1

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

Answer 2

SSIS jobs are the most appropriate for this usecase