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.

• JosefZ: You could run $query = "select * from " + $table; $expcsvS = invoke-sqlcmd -query $query ... before Get-ChildItem -Path $src -Recurse -File | ForEach-Object { Then I'd suppose that you can check ($_.BaseName + $_.Extension) -in $expcsvS.FILE_NAME or alike (I don't know FILE_NAME format).
• Scott Craner: @JosefZ write it up, it works and is much faster.

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 ) {}