I currently have a powershell that runs to update my data. I need to modify to delete all the rows in the dataset first and then get all new. I've NEVER used powershell so I''m a little lost. I found the code on a site and it works perfectly but I need to add in the piece to delete all rows first. Below is my script. Can someone PLEASE PLEASE add in the piece I need?
$dataSource = 'XXXXXX' #Server Name
$userName = 'XXXXX' #SQL User Name
$password = 'XXXXXX' #SQL User Password
$database = 'XXXXXXX' #Database Name
#Uncomment below connection string to use windows credentials
$connectionString = "Data Source=$dataSource;Initial Catalog=$database;Integrated Security=SSPI"
#Uncomment below connection string to use SQL Server authentication
#$connectionString ="Server=$dataSource;Database=$database;trusted_connection=false; user id =$userName;Password=$password;Integrated Security=False"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$query = '
SELECT * from BI_SOLD_RECAP;'
$command = $connection.CreateCommand()
$command.CommandText = $query
$dataset = $command.ExecuteReader()
#paste the endpoint URL which you have inside Power BI
$endpoint = "https://api.powerbi.com/beta/54e2bd33-9277-490e-9932-e1b5a2cfabb2/datasets/f0ee56bf-5c15-45e1-85a2-e..."
#push data to Power BI
while ($dataset.Read()) {
$payload = @{
"EventCode" =$dataset['EventCode']
"EventName" =$dataset['EventName']
"EventDetail" =$dataset['EventDetail']
"PerformanceID" =$dataset['PerformanceID']
"PerformanceName" =$dataset['PerformanceName']
"PerfDateTime" =$dataset['PerfDateTime']
"SalesChannel" =$dataset['SalesChannel']
"SaleDateOnly" =$dataset['SaleDateOnly']
"SaleDate_DOW" =$dataset['SaleDate_DOW']
"SaleDate_Month" =$dataset['SaleDate_Month']
"Quantity" =$dataset['Quantity']
"ServiceFees" =$dataset['ServiceFees']
"ServiceFeesQuantity" =$dataset['ServiceFeesQuantity']
"DeliveryFees" =$dataset['DeliveryFees']
"DeliveryFeesQuantity" =$dataset['DeliveryFeesQuantity']
"TicketCost" =$dataset['TicketCost']
"TotalAmount" =$dataset['TotalAmount']
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
}
$connection.Close();
$connection.Dispose();
