Store data from PowerShell to SQL Server Table in each different row

Question

I need help on PowerShell script, I want to store the data from the PowerShell output to SQL Server Table in different rows. Not in String format.

Powershell Query

$failoverGroups = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName $ResourceGroupName -ServerName $Server
$failoverGroupsNames=$failoverGroups.FailoverGroupName
$failoverGroupsNames

Write-Host "List of Failover Groups are : " $failoverGroupsNames

foreach($GroupNames in $failoverGroupsNames){
$failoverGroup = Get-AzSqlDatabaseFailoverGroup -ResourceGroupName $ResourceGroupName -ServerName $ServerName -FailoverGroupName $GroupNames
Write-Host $GroupNames

$DATABASENAME= $failoverGroup.DatabaseNames
Write-host "List of databases : " $DATABASENAME


$ResourceGroupName = $failoverGroup.ResourceGroupName


$insertfailover=@"
INSERT INTO FailoverGroupsDBNames (FAILOVERGROUPNAME,DATABASENAME,RESOURCEGROUP,SUBSCRIPTIONNAME)
VALUES('$GroupNames','$a','$ResourceGroupName','$SubscriptionName')
"@

$insertfailover_Conn=Invoke-Sqlcmd -ServerInstance $InsertServerName -Database $InsertDBName -Username $UserName -Password $Password -Query $insertfailover

}

I am getting the output like this. As a String in each row.

I want to output as Database name in each different row.

I don't follow the question. Also, your sample data is meaningless when you hide the entirety of it... Take the time to provide us with meaningful sample data, expected results, and a clear explanation of the problem you're having. — Thom A
– Thom A ♦, Commented Aug 9, 2021 at 8:49

Ezra · Accepted Answer · 2021-08-09 09:11:49Z

1

It appears that you are trying to split the Database name string, and insert a record for each:

$DATABASENAME.split(" ") | ForEach {

$insertfailover=@"INSERT INTO FailoverGroupsDBNames  
(FAILOVERGROUPNAME,DATABASENAME,RESOURCEGROUP,SUBSCRIPTIONNAME)
 VALUES('$GroupNames','$_','$ResourceGroupName','$SubscriptionName')"@

$insertfailover_Conn=Invoke-Sqlcmd -ServerInstance $InsertServerName -Database $InsertDBName -Username $UserName -Password $Password -Query $insertfailover

}

answered Aug 9, 2021 at 9:11

Ezra

5644 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Store data from PowerShell to SQL Server Table in each different row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related