PowerShell run SQL file with multiple queries and export the result to text file

Question

I have an SQL file with Multiple Queries. I run the SQL file by PowerShell and export the result to a Text file. The result only includes the first query result.

CREATE TABLE [MyDataBase].[dbo].[Persons] (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)

);

INSERT INTO [MyDataBase].[dbo].[Persons] (PersonID,LastName,FirstName,Address,City) VALUES( '101','LastnameA', 'FirstNameA', 'AddressA','CityA'),
( '102','LastnameB', 'FirstNameB', 'AddressB','CityB'),
( '103','LastnameC', 'FirstNameC', 'AddressC','CityC');

THe SQl file SQL_File is here

Select LastName from [MyDataBase].[dbo].[Persons] where PersonID ='101'; Select LastName from [MyDataBase].[dbo].[Persons] where PersonID ='102'

My PowerShell scripts code is here

$SQLServer = "MyService"

$Database = "MyDatabase"

$InputSQPath = "c:\myPath\SQL_File.sql"

$OutputSQFile ="c:\myPath\Myresult.txt"


Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -InputFile  $InputSQPath | Out-File -FilePath $OutputSQFile 

Actual result:

LastName

LastnameA

Expect Result: With all Results

LastName

LastnameA

FirstName

FirstnameB

Answer 1

Does the query have to be execute three separate times? Could you instead us an IN (and then ORDER BY), or a UNION?

Example:

select LastName from [MyDataBase].[dbo].[Persons] where PersonID in ('101','102','103) ORDER BY LastName;

OR

 Select LastName from [MyDataBase].[dbo].[Persons] where PersonID ='101'
UNION ALL
 Select LastName from [MyDataBase].[dbo].[Persons] where PersonID ='102'
UNION ALL
 Select LastName from [MyDataBase].[dbo].[Persons] where PersonID ='103'