sql-server - Powershell SQL合并输出？

Question

我已经清理了一些 powershell 来扫描目录列表的内容，然后插入到 MS SQL (2017) 表中而不创建重复项。

我无法根据当前目录的比较来修改 MERGE 以删除 sql 中的旧/孤立行：

   Add-Type -AssemblyName "System.Web"
function Get-FriendlySize {
    param($Bytes)
    $sizes='Bytes,KB,MB,GB,TB,PB,EB,ZB' -split ','
    for($i=0; ($Bytes -ge 1kb) -and 
        ($i -lt $sizes.Count); $i++) {$Bytes/=1kb}
    $N=2; if($i -eq 0) {$N=0}
    "{0:N$($N)} {1}" -f $Bytes, $sizes[$i]
}
 #Declare Servername
$sqlServer='localhost'
#Invoke-sqlcmd Connection string parameters
$params = @{'server'='localhost';'Database'='kentico'}

#Fucntion to manipulate the data
Function writeDiskInfo
{
param ($FileName, $FileTitle, $FileDescription,$FileExtension,$FileMimeType,$FilePath,$FileSize,$FileImageWidth,$FileImageHeight,$FileGUID,$FileLibraryID,$FileSiteID,$FileCreatedByUserID,$FileCreatedWhen,$FileModifiedByUserID, $FileModifiedWhen,$FileCustomData,$FileDate)
# Data preparation for loading data into SQL table 
$InsertResults = @"

Merge dbo.Media_File AS T

USING (SELECT  FileName = '$FileName') AS S 
ON S.FileName = T.FileName

WHEN NOT MATCHED BY TARGET THEN 
INSERT  (FileName,FileTitle,FileDescription,FileExtension,FileMimeType,FilePath,FileSize,FileImageWidth,FileImageHeight,FileGUID,FileLibraryID,FileSiteID,FileCreatedByUserID,FileCreatedWhen,FileModifiedByUserID,FileModifiedWhen,FileCustomData,FileDate)
VALUES ('$FileName','$FileTitle','$FileDescription','$FileExtension','$FileMimeType','$FilePath','$FileSize','$FileImageWidth','$FileImageHeight', '$FileGUID','$FileLibraryID','$FileSiteID','$FileCreatedByUserID','$FileCreatedWhen','$FileModifiedByUserID', '$FileModifiedWhen','$FileCustomData',CAST('$FileDate' AS datetime))

**This is where I get stuck**
WHEN NOT MATCHED BY SOURCE AND   
   (SELECT  FileName = '$FileName')  = T.FileName THEN
        DELETE;




"@      
#call the invoke-sqlcmdlet to execute the quer'
         Invoke-sqlcmd @params -Query $InsertResults
}

#Query WMI query to store the result in a varaible
$dp = (get-childitem  -Path   C:\inetpub\wwwroot\Kentico11\CMS\t\media\test\ -Recurse -file  |Select-Object -property @{Label='FileName';Expression={$_.baseName}},@{Label='FileTitle';Expression={$_.baseName}},@{N='FileDescription';E={"bob"}},@{Label='FileExtension';Expression={$_.Extension }},@{N='FileMimeType';E={[System.Web.MimeMapping]::GetMimeMapping("C:\inetpub\wwwroot\Kentico11\CMS\Enert\media\test\Compliance Analysis 2018-13-04.pdf")}},@{Label='FilePath';Expression= {($_.fullname.remove( 0, 58).replace( '\' ,'/'))}},@{N='FileSize';E={'1234'}},@{N='FileImageWidth';E={(Null)}},@{N='FileImageHeight';E={(Null)}},@{N='FileGUID';E={[guid]::newguid()}},@{N='FileLibraryID';E={("15")}},@{N='FileSiteID';E={("3")}},@{N='FileCreatedByUserID';E={("53")}},@{Label='FileCreatedWhen';Expression={$_.creationtime}},@{N='FileModifiedByUserID';E={("53")}}, @{Label='FileModifiedWhen';Expression={$_.creationtime}},@{N='FileCustomData';E={(Null)}},@{Label='FileDate';Expression={($_.baseName.Substring($_.basename.Length -13, 13))}}) 

#Loop through array
foreach ($item in $dp)
{
#Call the function to transform the data and prepare the data for insertion
writeDiskInfo $item.FileName $item.FileTitle $item.FileDescription $item.FileExtension $item.FileMimeType $item.FilePath $item.FileSize $item.FileImageWidth $item.FileImageHeight $item.FileGUID  $item.FileLibraryID $item.FileSiteID  $item.FileCreatedByUserID $item.FileCreatedWhen $item.FileModifiedByUserID $item.FileModifiedWhen $item.FileCustomData $item.FileDate  
}


  Invoke-Sqlcmd @params -Query "SELECT  * FROM Media_File" | format-table -AutoSize

标签： sql-serverpowershell