Shorten my powershell query code.

WeTi
WeTi used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
I see no reason why you cannot do that with one query with a bunch of joins. Can you share DDL?
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
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

Author

Commented:
oBdA  as great as usual, working after tryout.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial