question

siera_gld avatar image
siera_gld asked

Inserting names, path of directory into a SQL Table

I need to take the file name, and path and hopefully the file created date, maybe the updated date of a file and insert it into a sql table. so C:\MyFolder contains lots of directories and files and i would like to capture that in a sql table...is this possible? Thanks,
inserttabledirectoryinto
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
There are two good ways to accomplish this. Either create a CLR proc which will do what you need or a PowerShell function. Here's a rough PowerShell example that traverses the tree, grabs the file information and performs an insert into a table ---------- Add-PSSnapin SQL* $Path = '' $SubFolders = dir $Path -Recurse | Where-Object {$_.PSIsContainer} | ForEach-Object -Process {$_.FullName} ForEach ($Folder in $SubFolders) { $FullFileName = dir $Folder ForEach ($File in $FullFileName) { $FullFile = $File.FullName $FilePath = $File.DirectoryName $FileName = $File.Name $LastWritten = $File.LastWriteTime $InsertQuery = "INSERT INTO (FileName, DateModified) VALUES '$FullFile', '$LastWritten';" Invoke-Sqlcmd -ServerInstance -Database -Query $InsertQuery } }
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
You can use LogParser with something like : logparser "select name, path, creationtime, lastwritetime, lastaccesstime from c:\*.*" -i:fs -o:sql -database: -server: get LogParser at http://www.microsoft.com/download/en/details.aspx?id=24659
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.