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

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.

• 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). Dec 14 '18 at 13:18
• @JosefZ write it up, it works and is much faster. Dec 14 '18 at 14:53

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