How to create a table if not present using powershell

Question

i new to scripting and want to create SQL table if not present , and update its content. But when i try to run it throws an exception. Error Message: Exception calling "ExecuteReader" with "0" argument(s): "Invalid object name 'test.dbo.testStatus'."

I can't use Invoke Command for SQL query or any module based installation for SQL operation.

my code:

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
        }

What Changes should I make in above script , to create a table if not exist and insert data into it.

Any help will be thankfull.

Make the `CREATE TABLE` statement conditional: https://stackoverflow.com/a/6521016/712649 — Mathias R. Jessen, Sep 16 '22 at 12:09
@MathiasR.Jessen, Hi I used the Query shared by Laxmikant below. But it gives me an error.Exception calling "ExecuteReader" with "0" argument(s): "Invalid column name 'testStatus'." and not create a new table when there is no table named testStatus in db — Shubh kumar, Sep 19 '22 at 09:34

Laxmikant · Answer 1 · 2022-09-19T15:33:05.137

For inserting data you need to use ExecuteNonQuery, updated the checkquery and Execute commands

try{
        #SQL Query
        $CheckQuery="SELECT COUNT(1) AS TABLECOUNT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = '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.ExecuteNonQuery()
        $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.ExecuteNonQuery()
        
        #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.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
           #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
        }

@Laxmikant , above query throws Error Message: Exception calling "ExecuteReader" with "0" argument(s): "Invalid column name 'testStatus'." and not create a new table — Shubh kumar, Sep 19 '22 at 07:13
@Laxmikant, thanks for reply i have tested above code but it still gives me error if no table is present in db , also it is not going to else condition if table is not present. It simply comes inside if statement and throws error .Error Message: Exception calling "ExecuteNonQuery" with "0" argument(s): "Invalid object name 'TestDeployment.dbo.testFolderStatus2'." — Shubh kumar, Sep 20 '22 at 04:56
@Laxmikant, also when there is no table in db , $result gives count 1 for check query . so is there some way that i can read TABLECOUNT values. So that when its value is 1 i further continue — Shubh kumar, Sep 20 '22 at 07:40

How to create a table if not present using powershell

1 Answers1