Turn output into SQL Insert

Question

Turn output into SQL Insert

SSCrazy

Points: 2018

October 12, 2021 at 5:13 pm

I currently have this script writing to an output file, how can I turn this into a SQL insert to capture table counts with a another field

capturedate.

SQL table just has 3 fields:

Tbl_Name

Count

CaptureDate

Thanks

$CurrentDate = Get-Date
$CurrentDate = $CurrentDate.ToString('MM-dd-yyyy_hh-mm-ss')
function Get-DBTables{
    Param( 
        [Parameter(Mandatory)]
        [String]$ServerName, 
        [Parameter(Mandatory)]
        [String]$DatabaseName
    ) 


    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
    $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($ServerName) 
    $db = $srv.Databases.Item($DatabaseName)     
    $db.Tables
    
}
Get-DbTables srv1 cost | select Name, RowCountAsDouble | Export-Csv -Path c:\counts\cost_counts_$CurrentDate.csv -Encoding ascii -NoTypeInformation

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Ant-Green SSC Guru Points: 113445 More actions · Answer 2

Instead of writing my own powershell I’d use dbatools and look at Get-DbaDbTable to replace your code then pipe that to convert-dbadatatable and then write-dbadatatable