-Status ("Inner loop working on item [{0}]"-f$database) `
-Id 3`
-ParentId 2
$stepName="Decide on target schema/table names etc"
#--------------------------------------------
#If the input Query has target table information specified, use it, else generate using the query name
#InstanceName
#---------------------
if (($query|Get-Member|Select-Object Name |Where-Object{$_.Name.ToUpper() -eq'SAVERESULTSTOINSTANCE'}) -ne$null)
{
$saveToInstanceName=$query.SaveResultsToInstance
}
else
{
$saveToInstanceName=$SaveResultsToInstance
}
if ($saveToInstanceName.Trim().Length -eq0) { Throw"SaveResultsToInstance is empty. Specify it as a parameter or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#DatabaseName
#---------------------
if (($query|Get-Member|Select-Object Name |Where-Object{$_.Name.ToUpper() -eq'SAVERESULTSTODATABASE'}) -ne$null)
{
$saveToDatabaseName=$query.SaveResultsToDatabase
}
else
{
$saveToDatabaseName=$SaveResultsToDatabase
}
if ($saveToDatabaseName.Trim().Length -eq0) { Throw"SaveResultsToDatabase is empty. Specify it as a parameter or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#SchemaName
#---------------------
if (($query|Get-Member|Select-Object Name |Where-Object{$_.Name.ToUpper() -eq'SAVERESULTSTOSCHEMA'}) -ne$null)
{
$saveToSchemaName=$query.SaveResultsToSchema
}
else
{
$saveToSchemaName=$SaveResultsToSchema
}
if ($saveToSchemaName.Trim().Length -eq0) { Throw"SaveResultsToSchema is empty. Specify it as a parameter or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#TableName
#---------------------
if (($query|Get-Member|Select-Object Name |Where-Object{$_.Name.ToUpper() -eq'SAVERESULTSTOTABLE'}) -ne$null)
{
$saveToTableName=$query.SaveResultsToTable
}
else
{
$saveToTableName=$query.QueryTitle-Replace"[#?\{\[\(\)\]\}\ \,\.\']",'_'#Replace junk with underscore!
}
if ($saveToTableName.Trim().Length -eq0) { Throw"SaveResultsToTable is empty. Specify a non-empty QueryTitle for query or as an attribute with non-empty value in Queries input parameter! See examples for reference."}
#TruncateBeforeSave?
#---------------------
if (($query|Get-Member|Select-Object Name |Where-Object{$_.Name.ToUpper() -eq'SAVERESULTSTRUNCATEBEFORESAVE'}) -ne$null)
-WarningAction: SilentlyContinue #Supress warnings about columns whose datatypes cannot be converted
}
else
{
$stepName="Convert from object array to DataTable"
#--------------------------------------------
$dataTableWAddlCols=$resultsWAddlCols|
Out-DbaDataTable`
-WarningAction: SilentlyContinue #Supress warnings about columns whose datatypes cannot be converted
}
$stepName="Saving to: [{0}.{1}]"-f$saveToSchemaName,$saveToTableName
#--------------------------------------------
Write-Host$stepName
$invokeParams=@{
SqlInstance=$saveToInstanceName
InputObject=$dataTableWAddlCols
Database=$saveToDatabaseName
Schema=$saveToSchemaName
Table=$saveToTableName
AutoCreateTable=$true
Truncate=$saveTruncateBeforeSave
#Need to stop if table cannot be created or something similar
WarningAction=$CreateOutputTableWarningAction
}
if ($SaveToInstanceSqlCredential) {$invokeParams.Add('SqlCredential',$SaveToInstanceSqlCredential)}
#WARNING: Write-DbaDataTable currently has a bug if a schema other than 'dbo' is specified resulting in "WARNING: [Write-DbaDataTable][22:05:00] Schema does not exist."
Write-DbaDataTable@invokeParams
#No need to create PK if we are looping through subsequent db's on the same instance for same query!
if ($skipPK-eq$false)
{
#Create a PK only if the table does not already have a PK (if user modified it but knows what he/she is doing, we dont care to be anal).
$stepName="Check if PK exists on: [{0}]"-f$saveToTableName
#--------------------------------------------
#Will have issues if the input schema/table name has enclosing square brackets!
$sql="SELECT 1
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND EXISTS
(SELECT 1 FROM sys.tables t WHERE schema_name(t.schema_id) = TABLE_SCHEMA AND t.name = TABLE_NAME AND t.object_id = OBJECT_ID('{0}.{1}' ) "-f$saveToSchemaName,$saveToTableName
#Splat inputs (except SQL) and run the sql
$invokeParams=@{
ServerInstance=$saveToInstanceName
Database=$saveToDatabaseName
QueryTimeout=$QueryTimeout
ConnectionTimeout=$ConnectionTimeout
As="PSObject"
}
if ($SaveToInstanceSqlCredential) {$invokeParams.Add('Credential',$SaveToInstanceSqlCredential)}
$dataTable=Invoke-Sqlcmd2@invokeParams-Query $sql
if ($dataTable-eq$null)
{
#We need to create a PK on the table else subsequent Write-DbaDataTable will not APPEND data if it is still a HEAP
$stepName="Creating PK on: [{0}]"-f$saveToTableName
#--------------------------------------------
Write-Host$stepName
foreach($sqlin@(
(("ALTER TABLE {0} ALTER COLUMN CaptureSetID BIGINT NOT NULL `n"+
"ALTER TABLE {1} ALTER COLUMN captureSetLine BIGINT NOT NULL `n"