Shorten my powershell query code.

Dear experts

Is there a way to shorten the code below somehow? I need the $CustomerID to be alive and dynamic that i can change all the times and the $SSN as well.
You could save the query into a text file and use foreach syntax, but then the CustomerID in the text file will become static. Any idea?

$SQLServer = "SQLT01"
$SQLDBName = "Preprod"
$tempfolder = 'c:\test\test.csv'
$CustomerID = "466363464"
$SSN= "342564636"

function get-sqlquery{
	$SqlQuery1 = "
	Select FirstName as Name from Data_Person WHERE CustomerID = '$CustomerID'"
	$SqlQuery2 = "
    Select * from Data_Address WHERE CustomerID= '$CustomerID'"
    $SqlQuery3 = "
	Select * from Data_Email WHERE CustomerID= '$CustomerID'"
    $SqlQuery4 = "
    Select * from Data_Employment WHERE CustomerID= '$CustomerID'"
    $SqlQuery5 = "
    Select * from Data_Membership WHERE CustomerID= '$CustomerID'"
    $SqlQuery6 = "
    Select * from Data_Telephone WHERE CustomerID= '$CustomerID'"
	$SqlQuery7 = "
    Select * from Index_IncompleteCases where SSN= '$SSN'"
	$SqlQuery8 = "
    Select * from Data_CustomerNotes where SSN= '$SSN'"
	$SqlQuery9 = "
    Select * from Work WHERE ContactId= '$CustomerID'"
	$SqlQuery10 = "
    Select * from History_Work where pxHistoryForReference in (Select pzInsKey from Work where ContactId= '$CustomerID')"
	$SqlQuery11 = "
    Select * from Index_Interactions where ContactId= '$CustomerID'"
	$SqlQuery12 = "
    Select * from Data_MembershipHistory where CustomerID= '$CustomerID'"
    $SqlQuery13 = "
	Select * from Data_MembershipProgress where CustomerID= '$CustomerID'"
    $SqlQuery14 = "
	Select * from Data_WorkAttach where pxRefObjectKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
	$SqlQuery15 = "
    Select * from History_Work where pxHistoryForReference in (Select pzInsKey from Work where ContactId= '$CustomerID')"
	$SqlQuery16 = "
    Select * from Index_ToDoList where pxInsIndexedKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
	$SqlQuery17 = "
    Select * from Data_CaseProcessingTime where pyID in (Select pyID from Work where ContactId= '$CustomerID')"
	$SqlQuery18 = "
    Select * from data.pc_assign_workbasket where pxRefObjectKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
    $SqlQuery19 = "
	Select * from data.pc_assign_worklist where pxRefObjectKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
    $SqlQuery20 = "
	Select * from data.pr_sys_locks where pzInsKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
	$SqlQuery21 = "
    Select * from data.pc_link_attachment where pxLinkedRefFrom in (Select pzInsKey from Work where ContactId= '$CustomerID')"
	$SqlQuery22 = "
    Select * from Queue_ConnectCall where pyID in (Select pyID from Work where ContactId= '$CustomerID')"


$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; user id = OpsView; password = x7Ps5Q"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery1
$SqlCmd.Connection = $SqlConnection

$SqlCmd2 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd2.CommandText = $SqlQuery2
$SqlCmd2.Connection = $SqlConnection

$SqlCmd3 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd3.CommandText = $SqlQuery3
$SqlCmd3.Connection = $SqlConnection

$SqlCmd4 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd4.CommandText = $SqlQuery4
$SqlCmd4.Connection = $SqlConnection

$SqlCmd5 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd5.CommandText = $SqlQuery5
$SqlCmd5.Connection = $SqlConnection

$SqlCmd6 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd6.CommandText = $SqlQuery6
$SqlCmd6.Connection = $SqlConnection

$SqlCmd7 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd7.CommandText = $SqlQuery7
$SqlCmd7.Connection = $SqlConnection

$SqlCmd8 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd8.CommandText = $SqlQuery8
$SqlCmd8.Connection = $SqlConnection

$SqlCmd9 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd9.CommandText = $SqlQuery9
$SqlCmd9.Connection = $SqlConnection

$SqlCmd10 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd10.CommandText = $SqlQuery10
$SqlCmd10.Connection = $SqlConnection

$SqlCmd11 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd11.CommandText = $SqlQuery11
$SqlCmd11.Connection = $SqlConnection

$SqlCmd12 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd12.CommandText = $SqlQuery12
$SqlCmd12.Connection = $SqlConnection

$SqlCmd13 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd13.CommandText = $SqlQuery13
$SqlCmd13.Connection = $SqlConnection

$SqlCmd14 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd14.CommandText = $SqlQuery14
$SqlCmd14.Connection = $SqlConnection

$SqlCmd15 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd15.CommandText = $SqlQuery15
$SqlCmd15.Connection = $SqlConnection

$SqlCmd16 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd16.CommandText = $SqlQuery16
$SqlCmd16.Connection = $SqlConnection

$SqlCmd17 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd17.CommandText = $SqlQuery17
$SqlCmd17.Connection = $SqlConnection

$SqlCmd18 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd18.CommandText = $SqlQuery18
$SqlCmd18.Connection = $SqlConnection

$SqlCmd19 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd19.CommandText = $SqlQuery19
$SqlCmd19.Connection = $SqlConnection

$SqlCmd20 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd20.CommandText = $SqlQuery20
$SqlCmd20.Connection = $SqlConnection

$SqlCmd21 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd21.CommandText = $SqlQuery21
$SqlCmd21.Connection = $SqlConnection

$SqlCmd22 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd22.CommandText = $SqlQuery22
$SqlCmd22.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd 

$SqlAdapter2 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter2.SelectCommand = $SqlCmd2

$SqlAdapter3 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter3.SelectCommand = $SqlCmd3

$SqlAdapter4 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter4.SelectCommand = $SqlCmd4

$SqlAdapter5 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter5.SelectCommand = $SqlCmd5

$SqlAdapter6 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter6.SelectCommand = $SqlCmd6

$SqlAdapter7 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter7.SelectCommand = $SqlCmd7

$SqlAdapter8 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter8.SelectCommand = $SqlCmd8

$SqlAdapter9 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter9.SelectCommand = $SqlCmd9

$SqlAdapter10 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter10.SelectCommand = $SqlCmd10

$SqlAdapter11 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter11.SelectCommand = $SqlCmd11

$SqlAdapter12 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter12.SelectCommand = $SqlCmd12

$SqlAdapter13 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter13.SelectCommand = $SqlCmd13

$SqlAdapter14 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter14.SelectCommand = $SqlCmd14

$SqlAdapter15 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter15.SelectCommand = $SqlCmd15

$SqlAdapter16 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter16.SelectCommand = $SqlCmd16

$SqlAdapter17 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter17.SelectCommand = $SqlCmd17

$SqlAdapter18 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter18.SelectCommand = $SqlCmd18

$SqlAdapter19 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter19.SelectCommand = $SqlCmd19

$SqlAdapter20 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter20.SelectCommand = $SqlCmd20

$SqlAdapter21 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter21.SelectCommand = $SqlCmd21

$SqlAdapter22 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter22.SelectCommand = $SqlCmd22

$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter.Fill($DataSet)
$DataSet2 = New-Object System.Data.DataSet
[void]$SqlAdapter2.Fill($DataSet)
$DataSet3 = New-Object System.Data.DataSet
[void]$SqlAdapter3.Fill($DataSet)
$DataSet4 = New-Object System.Data.DataSet
[void]$SqlAdapter4.Fill($DataSet)
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter5.Fill($DataSet)
$DataSet2 = New-Object System.Data.DataSet
[void]$SqlAdapter6.Fill($DataSet)
$DataSet3 = New-Object System.Data.DataSet
[void]$SqlAdapter7.Fill($DataSet)
$DataSet4 = New-Object System.Data.DataSet
[void]$SqlAdapter8.Fill($DataSet)
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter9.Fill($DataSet)
$DataSet2 = New-Object System.Data.DataSet
[void]$SqlAdapter10.Fill($DataSet)
$DataSet3 = New-Object System.Data.DataSet
[void]$SqlAdapter11.Fill($DataSet)
$DataSet4 = New-Object System.Data.DataSet
[void]$SqlAdapter12.Fill($DataSet)
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter13.Fill($DataSet)
$DataSet2 = New-Object System.Data.DataSet
[void]$SqlAdapter14.Fill($DataSet)
$DataSet3 = New-Object System.Data.DataSet
[void]$SqlAdapter15.Fill($DataSet)
$DataSet4 = New-Object System.Data.DataSet
[void]$SqlAdapter16.Fill($DataSet)
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter17.Fill($DataSet)
$DataSet2 = New-Object System.Data.DataSet
[void]$SqlAdapter18.Fill($DataSet)
$DataSet3 = New-Object System.Data.DataSet
[void]$SqlAdapter19.Fill($DataSet)
$DataSet4 = New-Object System.Data.DataSet
[void]$SqlAdapter20.Fill($DataSet)
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter21.Fill($DataSet)
$DataSet2 = New-Object System.Data.DataSet
[void]$SqlAdapter22.Fill($DataSet)


 $SqlConnection.Close
return $DataSet.Tables[0].Rows
}
$sqlArr = @()
get-sqlquery | Export-Csv -NoTypeInformation -Verbose -encoding default -Path $tempfolder

Open in new window

LVL 1
WeTiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun VermaakTechnical SpecialistCommented:
I see no reason why you cannot do that with one query with a bunch of joins. Can you share DDL?
oBdACommented:
Depending on what you actually want to achieve, joining the queries might be the better solution, but for the time being, here is your initial solution, with all the duplicated code removed.
Heavily untested, and you need to set the new (because you will need to change the current one) password for the user id ...
$CustomerID = "466363464"
$SSN= "342564636"
$tempCsv = 'c:\test\test.csv'

Function Get-SqlQuery {
[CmdletBinding()]
Param(
	[string]$SQLServer = "SQLT01",
	[string]$SQLDBName = "Preprod",
	[string]$CustomerID,
	[string]$SSN,
	[string]$UserId = "OpsView",
	[string]$Password = "TopSecret"
)
	$SqlQueries = @(
		"Select FirstName as Name from Data_Person WHERE CustomerID = '$CustomerID'"
		"Select * from Data_Address WHERE CustomerID= '$CustomerID'"
		"Select * from Data_Email WHERE CustomerID= '$CustomerID'"
		"Select * from Data_Employment WHERE CustomerID= '$CustomerID'"
		"Select * from Data_Membership WHERE CustomerID= '$CustomerID'"
		"Select * from Data_Telephone WHERE CustomerID= '$CustomerID'"
		"Select * from Index_IncompleteCases where SSN= '$SSN'"
		"Select * from Data_CustomerNotes where SSN= '$SSN'"
		"Select * from Work WHERE ContactId= '$CustomerID'"
		"Select * from History_Work where pxHistoryForReference in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from Index_Interactions where ContactId= '$CustomerID'"
		"Select * from Data_MembershipHistory where CustomerID= '$CustomerID'"
		"Select * from Data_MembershipProgress where CustomerID= '$CustomerID'"
		"Select * from Data_WorkAttach where pxRefObjectKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from History_Work where pxHistoryForReference in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from Index_ToDoList where pxInsIndexedKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from Data_CaseProcessingTime where pyID in (Select pyID from Work where ContactId= '$CustomerID')"
		"Select * from data.pc_assign_workbasket where pxRefObjectKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from data.pc_assign_worklist where pxRefObjectKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from data.pr_sys_locks where pzInsKey in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from data.pc_link_attachment where pxLinkedRefFrom in (Select pzInsKey from Work where ContactId= '$CustomerID')"
		"Select * from Queue_ConnectCall where pyID in (Select pyID from Work where ContactId= '$CustomerID')"
	)
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = '$($SQLServer)'; Database = '$($SQLDBName)'; user id = '$($UserID)'; password = '$($Password)'"
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.Connection = $SqlConnection
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$DataTable = New-Object System.Data.DataTable
	ForEach ($SqlQuery In $SqlQueries) {
		$SqlCmd.CommandText = $SqlQuery
		$SqlAdapter.SelectCommand = $SqlCmd
		[void]$SqlAdapter.Fill($DataTable)
	}
	[void]$SqlConnection.Close()
	Return $DataTable.Rows
}

Get-SqlQuery -CustomerID $CustomerID -SSN $SSN | Export-Csv -NoTypeInformation -Verbose -Encoding Default -Path $tempCsv

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
oBdA  as great as usual, working after tryout.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.