PowerShell实现SQL表不存在则创建并插入数据的报错解决

脚本新手想要实现SQL表不存在则创建并插入数据的功能，但运行脚本时抛出异常：
Exception calling "ExecuteReader" with "0" argument(s): "Invalid object name 'test.dbo.testStatus'."
限制条件：无法使用Invoke-Command执行SQL查询，也不能安装任何SQL操作相关模块。原代码如下：

try{
        #SQL Query
        $CheckQuery='SELECT COUNT(1) FROM [test].[dbo].[testStatus]'
        $InsertQuery="INSERT INTO [test].[dbo].[testStatus]
                   ([DATE],[HOST_NAME],[FOLDER_NAME],[FOLDER_PATH],[STATUS])
             VALUES('$date','$ComputerName','$name','$path','$status')
        "
        $CreateQuery='CREATE TABLE testStatus (DATE DATE,HOST_NAME NVARCHAR(MAX),FOLDER_NAME NVARCHAR(MAX),FOLDER_PATH NVARCHAR(MAX),STATUS NVARCHAR(MAX))'
        
        
        $connString = "Data Source=$SqlServer;Database=$Database;User ID=$SqlAuthLogin;Password=$SqlAuthPw"
        
        #Create a SQL connection object
        $conn = New-Object System.Data.SqlClient.SqlConnection $connString
        
        #Attempt to open the connection
        $conn.Open()
        if($conn.State -eq "Open")
        {
        Write-Host "Test connection successfull"
        "$(get-date -format "yyyy-MM-dd HH:mm:ss"):Connection Established" | out-file $LogFile -Append
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
        $SqlCmd.CommandText = $CheckQuery  
        $SqlCmd.Connection = $conn  
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
        $SqlAdapter.SelectCommand = $SqlCmd   
        #Creating Dataset  
        $Datatable = New-Object "System.Data.Datatable"
        $result = $SqlCmd.ExecuteReader()
        $Datatable.Load($result)
        $TableExistCheck=$Datatable
        if($TableExistCheck)
        {
        
        #Insert into Table
        $SqlCmd.CommandText = $InsertQuery  
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
        $SqlAdapter.SelectCommand = $SqlCmd   
        #Creating Dataset  
        $Datatable = New-Object "System.Data.Datatable"
        $result = $SqlCmd.ExecuteReader()
        $conn.Close()
        "$(get-date -format "yyyy-MM-dd HH:mm:ss"):Successfully Inserted Data" | out-file $LogFile -Append
        }
        else{
        #Create Table
        $SqlCmd.CommandText = $CreateQuery  
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
        $SqlAdapter.SelectCommand = $SqlCmd  
        #Creating Dataset  
        $Datatable = New-Object "System.Data.Datatable"
        $result = $SqlCmd.ExecuteReader()
        
        #Insert into Table
        $SqlCmd.CommandText = $InsertQuery  
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter  
        $SqlAdapter.SelectCommand = $SqlCmd   
        #Creating Dataset  
        $Datatable = New-Object "System.Data.Datatable"
        $result = $SqlCmd.ExecuteReader()
        $conn.Close()
        "$(get-date -format "yyyy-MM-dd HH:mm:ss"):Successfully Created Table and Inserted Data" | out-file $LogFile -Append
        }
        }  
        }
        catch
        {
           Write-Host "`nError Message: " $_.Exception.Message
           Write-Host "`nError in Line: " $_.InvocationInfo.Line
           Write-Host "`nError in Line Number: "$_.InvocationInfo.ScriptLineNumber
           Write-Host "`nError Item Name: "$_.Exception.ItemName
           #Set-Content -Path $LogFile -Value $_.Exception.Message
           #LogMessage -Message "Error:" $_.Exception.Message;
           "$(get-date -format "yyyy-MM-dd HH:mm:ss"):$($_.Exception.Message)" | out-file $LogFile -Append
        }

修改方案 #

核心问题分析 #

原脚本直接查询目标表判断存在性，当表不存在时会直接抛出异常进入catch块，无法执行后续创建表逻辑；同时创建表时未指定架构，可能导致表创建在非dbo架构下，后续插入仍会报错。

关键修改点 #

1. 替换表存在性检查逻辑：改用查询SQL系统视图sys.tables判断表是否存在，避免直接查询不存在的表抛出异常。
2. 指定创建表的架构：在CREATE TABLE语句中明确指定dbo架构，确保表创建在正确位置。
3. 优化SQL命令执行方式：创建表和插入数据属于非查询操作，使用ExecuteNonQuery()替代ExecuteReader()，更高效且符合操作类型。
4. 简化数据读取逻辑：查询表存在性时，直接用ExecuteScalar()获取结果，无需创建DataTable，简化代码。

修改后的完整代码 #

try{
    # SQL Query
    # 改用系统视图查询表是否存在，指定数据库和架构
    $CheckQuery = "SELECT COUNT(1) FROM [test].[sys].[tables] WHERE name = 'testStatus' AND schema_id = SCHEMA_ID('dbo')"
    $InsertQuery = "INSERT INTO [test].[dbo].[testStatus]
                   ([DATE],[HOST_NAME],[FOLDER_NAME],[FOLDER_PATH],[STATUS])
             VALUES('$date','$ComputerName','$name','$path','$status')
    "
    # 创建表时明确指定dbo架构
    $CreateQuery = "CREATE TABLE [test].[dbo].[testStatus] (DATE DATE,HOST_NAME NVARCHAR(MAX),FOLDER_NAME NVARCHAR(MAX),FOLDER_PATH NVARCHAR(MAX),STATUS NVARCHAR(MAX))"
    
    $connString = "Data Source=$SqlServer;Database=$Database;User ID=$SqlAuthLogin;Password=$SqlAuthPw"
    
    # 创建SQL连接对象
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString
    
    # 尝试打开连接
    $conn.Open()
    if($conn.State -eq "Open") {
        Write-Host "Test connection successful"
        "$(get-date -format "yyyy-MM-dd HH:mm:ss"):Connection Established" | Out-File $LogFile -Append
        
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand  
        $SqlCmd.Connection = $conn  

        # 检查表是否存在
        $SqlCmd.CommandText = $CheckQuery
        $tableExists = [bool]$SqlCmd.ExecuteScalar()

        if($tableExists) {
            # 插入数据，使用ExecuteNonQuery执行非查询操作
            $SqlCmd.CommandText = $InsertQuery
            $SqlCmd.ExecuteNonQuery()
            $conn.Close()
            "$(get-date -format "yyyy-MM-dd HH:mm:ss"):Successfully Inserted Data" | Out-File $LogFile -Append
        }
        else {
            # 创建表
            $SqlCmd.CommandText = $CreateQuery
            $SqlCmd.ExecuteNonQuery()
            
            # 插入数据
            $SqlCmd.CommandText = $InsertQuery
            $SqlCmd.ExecuteNonQuery()
            
            $conn.Close()
            "$(get-date -format "yyyy-MM-dd HH:mm:ss"):Successfully Created Table and Inserted Data" | Out-File $LogFile -Append
        }
    }  
}
catch {
    Write-Host "`nError Message: " $_.Exception.Message
    Write-Host "`nError in Line: " $_.InvocationInfo.Line
    Write-Host "`nError in Line Number: "$_.InvocationInfo.ScriptLineNumber
    Write-Host "`nError Item Name: "$_.Exception.ItemName
    "$(get-date -format "yyyy-MM-dd HH:mm:ss"):$($_.Exception.Message)" | Out-File $LogFile -Append
}

内容的提问来源于stack exchange，提问作者Shubh kumar