Home Forums SQL Server 2005 T-SQL (SS2K5) Scripting help - Need a query to find filenames and filepaths under a certain directory RE: Scripting help - Need a query to find filenames and filepaths under a certain directory

  • October 27, 2009 at 4:07 pm

    #1071497

    this was an order of magnitude faster: 8 minutes to create a text file to disk, then 20 seconds to bulk insert into a table

    using the /b command, it was only 1 minute 11 secs to create the file to disk.

    CREATE TABLE #Results (txtResult varchar(2000) NULL)

    INSERT INTO #Results (txtResult)

    EXEC master.dbo.xp_cmdshell 'DIR "C:\PROGRAM FILES\*.*" /b s >C:\contents.txt'

    BULK INSERT #Results FROM 'C:\contents.txt'

    WITH (

    DATAFILETYPE = 'char',

    FIELDTERMINATOR = ',',

    ROWTERMINATOR = '',

    FIRSTROW = 1

    )

    SELECT * FROM #Results

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!