Powershell command to have separate column in output file

Mario2286 421 Reputation points

I m using this Dbatools powershell command to select a table from two same database in both instance as below

Query: SELECT TOP (1000) [name]
FROM [AdventureWorks2017].[dbo].[table11]

Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -AppendServerInstance | Out-File C:\b2\output1.txt

and the results I get is this


for every server in the list, I need it to have separate column name , I m supposed to get results as below


Appreciate your expertise to advice me what need to add in the scripts

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,857 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
4,901 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 19,141 Reputation points Microsoft Vendor


    Does this work for you?

    $servers = 'DESKTOP-6U9IKQD','DESKTOP-6U9IKQD\bm'  
    $result = Invoke-DbaQuery -ServerInstance $InstanceList -Query $sql -AppendServerInstance  
    foreach($server in $servers){  
        $result | Where-Object {$_.ServerInstance -eq $server} | Out-File -FilePath C:\b2\output1.txt -Append  

    Best Regards,
    Ian Xue


    If the Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 29,121 Reputation points

    Use the function SERVERNAME

    SELECT TOP (1000) [name]
    FROM [AdventureWorks2017].[dbo].[table11]

  2. Mario2286 421 Reputation points

    Any idea will be helpful to me

    0 comments No comments