Quickly check whether file name is in column in an SQL Table and move to new folder

Question

I have a table that in one of the columns exists a list of file names. The names comes from other tables and the files in a folder.

Not all the files are used, may be attached to parcels that no longer are active. My list is of only the active parcels.

After creating the table I want to go through the folder of files and move the files that are being used(exist in the table) to another folder.

Currently I iterate the files in the folder and test whether each file one at a time returns a record or not using an SQL command.

This works, but the consistent checking back to the database is slow.

Here is the code as it sits now:

param( 
    [string] $src,  
    [string] $dest, 
    [string] $table
    )

$sql_instance_name = 'db'
$db_name = 'DB2'
$sql_user = 'user'
$sql_user_pswd = 'password'



Get-ChildItem -Path $src -Recurse -File | ForEach-Object  {
    $query = "select * from " + $table + " WHERE FILE_NAME Like '" + $_.BaseName + $_.Extension +  "'"
    #write-output $query


    $expcsv = invoke-sqlcmd -Username $sql_user -PASSWORD $sql_user_pswd -Database $db_name -Query $query -serverinstance $sql_instance_name
    if($expcsv -ne $null)

    {
        $nextName = Join-Path -Path $dest ( $_.BaseName  +  $_.EXTENSION)
        Write-Output $nextName
        $_ | Move-Item -Destination   $nextName
    }
}

Is there a way to load the data into memory, and search that instead of going back and forth from the database? I have to assume it would be quicker.

Answer 1

Output from invoke-sqlcmd is Formatted table i.e. something like PSCustomObject[] array (or collection). Hence, one could address its columns as .FILE_NAME properties and apply comparison operators -in, -contains, -match etc. (elaborated from my original comment):

param( 
    [string] $src,  
    [string] $dest, 
    [string] $table
    )

$sql_instance_name = 'db'
$db_name = 'DB2'
$sql_user = 'user'
$sql_user_pswd = 'password'

$query = "select * from $table"
$expcsvS = invoke-sqlcmd -Username $sql_user -PASSWORD $sql_user_pswd `
    -Database $db_name -Query $query -serverinstance $sql_instance_name

Get-ChildItem -Path $src -Recurse -File | ForEach-Object  {

    if ( $_.Name -in  $expcsvS.FILE_NAME )
    {
        $nextName = Join-Path -Path $dest -ChildPath $_.Name
        Write-Output $nextName
        $_ | Move-Item -Destination $nextName
    }
}

Note that I use $_.Name instead of ($_.BaseName + $_.Extension) as for files, the following code snippet always returns True.

(Get-ChildItem -Recurse -File | 
    Where-Object { $_.Name -ne ($_.BaseName + $_.Extension) }) -eq $null

Also note that if ( $_.Name -in $expcsvS.FILE_NAME ) {} is equivalent to any of (incomplete list):

if ( $expcsvS.FILE_NAME -match "^$($_.Name)$") {}
if ( $expcsvS.FILE_NAME -contains $_.Name ) {}