compare files in directory to SQL table

  • I would like to write a stored procedure that looks at all .PDF files in a given directory on a server and checks if a record with that filename exists on a particular SQL table. If not, the SP would then create a new record in the SQL table. Can anyone tell me if this is possible and, if so, suggest a good way to do it?

    Thanks...PC

  • Actually with the complications of doing it with an SP and permissions I always found it easier to use DTS with an Active Script object. In it use FIleSystemObjects and look for the files and do all you need. DOn't have example handy but there are plenty on the web. Even try http://www.sqldts.com , unless you are adamant about doing in an SP.

  • It's easy to do in .Net. As long as the server is of a decent spec, you should have too much trouble. I've done something similiar myself in the past month or so.

    Have a look around on http://www.csharpfriends.com and you should be able to figure it out.

  • If you want to do this using a stored procedure, maybe the code below will help...

    DECLARE @RowCnt int

    CREATE TABLE #DirList (RowId int IDENTITY (1,1), OneField varchar(2000))

    INSERT INTO #DirList

      EXEC master..xp_cmdshell 'dir \\SERVERNAME\DIRECTORY(Shared If Required)\*.*'

    SET @RowCnt = @@ROWCOUNT

    SELECT SUBSTRING(OneField, 40, LEN(OneField)-39)

    FROM #DirList

    WHERE SUBSTRING(OneField, 40, 20) NOT IN ('.', '..', ' bytes', ' bytes free')

    AND RowID BETWEEN 6 AND (@RowCnt - 3)

    AND SUBSTRING(OneField, 25, 5) NOT LIKE '<DIR>'

    /*

    Compare output above with the DB Table value and

    do insert if required

    */

    DROP TABLE #DirList

     

     


    Nikki Pratt
    Development DBA

  • change this to : (add switches /b/s)

     EXEC master..xp_cmdshell 'dir \\SERVERNAME\DIRECTORY(Shared If Required)\*.PDF /b/s'

    change *.* to *.PDF for a bit of a performance boost

    and you can get rid of the complicated WHERE clause in the succeeding SQL statement.  Regards

    also... adding the /b/s will generate 1 null record.  delete that 1 null record before joining the results to your table.

    You can eliminate this complex query below

    SELECT SUBSTRING(OneField, 40, LEN(OneField)-39)

    FROM #DirList

    WHERE SUBSTRING(OneField, 40, 20) NOT IN ('.', '..', ' bytes', ' bytes free')

    AND RowID BETWEEN 6 AND (@RowCnt - 3)

    AND SUBSTRING(OneField, 25, 5) NOT LIKE '<DIR>'

    By using Select * from #DirList. 

    join (left/right whatever you fancy) to your table to find what's missing

  • Thank you ANTARES686, PINHEAD and NPRATT. With the references and scripts you all offered, I'm well on my way to accomplishing the task. Your help is very much appreciated!

    PC:

  • In Cold Fusion this is literally about 3 lines of code.

    That is a FACT!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply