Create temp table using xp_cmdshell in MS SQL Server 2000

olDR1i used Ask the Experts™
I am creating a stored procedure that will update a table from the current contents of a specified directory based on the last time the stored proc ran. I am using the following to create the temp table:

        create table #tmp(result varchar(255))
        insert into #tmp exec xp_cmdshell 'dir c:\[path]'

However, this returns the results of the 'dir' cmd to one result column and, the only data that I need is the file names. I'm not positive this is even the best method to achieve my desired results. Does anyone have comments or an alternate solution?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Just a quick hint -- use the /b option on your DIR command, and all you will get is the file names:

       create table #tmp(result varchar(255))
       insert into #tmp exec xp_cmdshell 'dir c:\[path] /b'
Strange - 3rd time today I've given out this code

create table #Dir (s varchar(8000))
      select      @cmd = 'dir /B ' + @FilePath + @FileNameMask
      delete #Dir
      insert #Dir exec master..xp_cmdshell @cmd
      delete #Dir where s is null or s like '%not found%'
      while exists (select * from #Dir)
            select       @FileName = min(s) from #Dir
            select      @File = @FilePath + @FileName
(the delete #dir is because I use the same temp table for multiple directories. Not needed if you only need it once.)


There I go again, over thinking a problem.  Thanks for the reminder :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial