如果不再存在，如何从数据库中删除记录

发布于 2025-02-05 21:28:54 字数 1082 浏览 4 评论 0原文

如果我在CSV文件中有新的数据/记录，则使用这样的PowerShell将其导入SQL Server数据库。

$CSVImport = Import-CSV $Global:ErrorReport
        ForEach ($CSVLine1 in $CSVImport) {
            $CSVHold1 = $CSVLine1.Hold
            $CSVGSID1 = $CSVLine1.GSID 
            $CSVSource1 = $CSVLine1.Source
            $CSVTYPE1 = $CSVLine1.TYPE
            $CSVMessage1 = $CSVLine1.Message
            $CSVCreatedDate1 = $CSVLine1.Time


            $query = "USE $Global:Database
               IF NOT EXISTS (SELECT * FROM Lit_Hold_Err 
                    WHERE GSID = '$CSVGSID1'
                    AND Source = '$($CSVSource1 -replace "'","''")')
                BEGIN
                INSERT INTO Lit_Hold_Err (Hold, GSID, Source, Error, Type,Date)
                VALUES('$CSVHold1', '$CSVGSID1', '$($CSVSource1 -replace "'","''")','$CSVMessage1','$CSVTYPE1', '$CSVCreatedDate1') 
                END"
           
        Invoke-Sqlcmd -Query $query -ServerInstance $Global:Server -Database $Global:Database

我只是想知道，如果我的CSV文件中不再存在？

我知道我需要使用删除语句，但我有点卡在子句上，因此任何帮助或建议都将不胜感激。

原文

If I have a new data/record in my CSV file then I'm importing it to my SQL Server database using PowerShell like this.

$CSVImport = Import-CSV $Global:ErrorReport
        ForEach ($CSVLine1 in $CSVImport) {
            $CSVHold1 = $CSVLine1.Hold
            $CSVGSID1 = $CSVLine1.GSID 
            $CSVSource1 = $CSVLine1.Source
            $CSVTYPE1 = $CSVLine1.TYPE
            $CSVMessage1 = $CSVLine1.Message
            $CSVCreatedDate1 = $CSVLine1.Time


            $query = "USE $Global:Database
               IF NOT EXISTS (SELECT * FROM Lit_Hold_Err 
                    WHERE GSID = '$CSVGSID1'
                    AND Source = '$($CSVSource1 -replace "'","''")')
                BEGIN
                INSERT INTO Lit_Hold_Err (Hold, GSID, Source, Error, Type,Date)
                VALUES('$CSVHold1', '$CSVGSID1', '$($CSVSource1 -replace "'","''")','$CSVMessage1','$CSVTYPE1', '$CSVCreatedDate1') 
                END"
           
        Invoke-Sqlcmd -Query $query -ServerInstance $Global:Server -Database $Global:Database

I'm just wondering how should I write another query that compare my CSV file with my database and delete a particular/s record from my database if it's no longer existed in my CSV file?.

I know I'll need to use DELETE Statement but I'm kinda stuck on WHERE Clause so Any help or suggestion would be really appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

╭ゆ眷念 2025-02-12 21:28:54

一个建议将使用 dbatool的import-dbacsv 读取CSV文件并将其加载到一个tempdb上的表，然后使用合并指令来制作插入/更新/删除。

导入命令将是：

$Credential = Get-Credential  
Import-DbaCsv -Path .\test.csv -AutoCreateTable -SqlInstance my-instance -Database tempdb -SqlCredential $Credential -SupportsMultiline

A suggestion would be using DBATool's Import-DbaCsv to read the CSV file and bulk load it to a table on tempdb, then use a merge instruction to make the inserts/updates/deletes.

The import command would be something like:

$Credential = Get-Credential  
Import-DbaCsv -Path .\test.csv -AutoCreateTable -SqlInstance my-instance -Database tempdb -SqlCredential $Credential -SupportsMultiline

回复收藏 0 原文

~没有更多了~