Created
October 30, 2017 07:37
-
-
Save piers7/10b7a6a2794424faea28a3ea6d98e9ff to your computer and use it in GitHub Desktop.
Generates a reference data insert/update script, based on contents of an existing table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
.Synopsis | |
Generates a table-sync script, based on the current contents of a table | |
#> | |
param( | |
[Parameter(Mandatory=$true)] | |
$table, | |
$schema = 'Dim', | |
$database = 'ITS_DataMart', | |
$server = 'localhost', | |
[switch]$setIdentityCols = $true, | |
[switch]$noUpdate | |
) | |
# This script generates a data script that can be used in a merge-rebuild of the table contents | |
# There's a certain amount of over-engineering here because I cut-and-pasted a lot of it | |
# from a more complicated script that had to deal with other things | |
# However catering for non-write columns (like timestamp, identity, calculated) is not a bad thing, so I left it in | |
$erroractionpreference = 'stop'; | |
$crlf = [Environment]::NewLine | |
function ExecuteCommand( | |
$command = { throw 'Command must be supplied' } | |
){ | |
$rows = @(); | |
$reader = $command.executereader() | |
while($reader.read()){ | |
$values = new-object object; | |
for($i = 0; $i -lt $reader.FieldCount; $i++){ | |
$name = $reader.GetName($i); | |
$value = $reader.GetValue($i); | |
Add-Member -in $values noteproperty $name $value | |
} | |
$rows+= $values; | |
} | |
$reader.Close(); | |
$rows | |
} | |
function ExecuteSql( | |
[string] $sql = { throw 'sql must be specified' } | |
) | |
{ | |
$command = $conn.CreateCommand(); | |
$command.CommandText = $sql; | |
ExecuteCommand $command; | |
} | |
function ColumnsAsCSList($columns, $prefix, $suffix){ | |
[String]::Join(', ', @( $columns | % { "$prefix[" + $_.column_name + "]$suffix" } )) | |
} | |
function ColumnsAsList($columns, $delim, $prefix, $suffix){ | |
[String]::Join($delim, @( $columns | % { "$prefix[" + $_.column_name + "]$suffix" } )) | |
} | |
function CreateColumnList( | |
[string[]]$columns, | |
$prefix, | |
$seperator = ', ' | |
){ | |
$columns = $columns | % { "$prefix[$_]" } | |
[string]::Join($seperator,$columns) | |
} | |
function CreatePredicate( | |
[string[]]$columns, | |
$leftPrefix, | |
$rightPrefix, | |
$operator = '<>', | |
$aggregator = " OR " | |
){ | |
$predicates = @($columns | % { '({0}[{1}] {4} {2}[{3}])' -f $leftPrefix,$_,$rightPrefix,$_,$operator }); | |
if($predicates.length -gt 0){ | |
[string]::Join($aggregator, $predicates); | |
}else{ | |
'(1=1)' # no-op predicate to simplify building WHERE clauses | |
} | |
} | |
function CreateSetter( | |
[string[]]$columns, | |
$leftPrefix, | |
$rightPrefix, | |
$delimiter = "`n`t," | |
){ | |
$setters = $columns | % { '{0}[{1}] = {2}[{3}]' -f $leftPrefix,$_,$rightPrefix,$_ } | |
[string]::Join($delimiter, $setters); | |
} | |
$simpleFormats = @{ | |
'System.TimeSpan'="'{0:HH:mm:ss}'"; | |
'System.DateTime'="'{0:u}'"; | |
'System.DateTimeOffset'="'{0:u}'"; | |
} | |
function GetDataForInsert($sql){ | |
# This is what we are aiming for | |
$void = @" | |
(1, 'Update Firmware', 'Not currently supported', 0) | |
, (2, 'Restart', 'Reboot the DeviceController', 1) | |
, (3, 'Reset Time', 'Set the DeviceController''s date and time', 1) | |
, (4, 'Reconfigure', 'Set one or more configuration values', 1) | |
, (5, 'Test Detectors', 'Get diagnostic information for all detectors attached to the DeviceController', 1) | |
, (6, 'Test Battery Voltage', 'Get the current battery voltage in the DeviceController', 1) | |
"@ | |
$command = $conn.CreateCommand(); | |
$command.CommandText = $sql; | |
$prefix = " "; | |
$reader = $command.executereader() | |
while($reader.read()){ | |
[string[]] $values = @(); | |
for($i = 0; $i -lt $reader.FieldCount; $i++){ | |
if($reader.IsDbNull($i)){ | |
$values += 'null'; | |
continue; | |
} | |
# $name = $reader.GetName($i); | |
$value = $reader.GetValue($i); | |
$datatype = $reader.GetFieldType($i); | |
$format = $simpleFormats[$datatype.FullName]; | |
if($format){ | |
$values += $format -f $value; | |
}elseif ($datatype -eq [system.boolean]){ | |
$values += [Convert]::ToInt16($value); | |
}elseif ($datatype -eq [system.string]){ | |
$values += "'{0}'" -f ($value -replace "'","''"); | |
}else{ | |
$values += "{0}" -f $value | |
} | |
} | |
$prefix + "(" + [string]::Join(",`t", $values) + ")$crlf" | |
$prefix = ", " | |
} | |
$reader.Close(); | |
} | |
$conn = new-object System.Data.SqlClient.SqlConnection; | |
$conn.ConnectionString = "data source=$server;initial catalog=$database;trusted_connection=true"; | |
$conn.Open(); | |
$getColumnsSql = @" | |
SELECT [Schema_Name] | |
,[Table_Name] | |
,[Column_Name] | |
,[system_type_id] | |
,[Type_Name] | |
,[is_identity] | |
,[is_computed] | |
,[Is_Business_Key] | |
,[Is_Concurrency_Indicator] | |
,[Is_Primary_Key] | |
FROM [Admin].[TablesAndColumnsWithBusinessKeys] | |
WHERE [Schema_Name]='$schema' AND [TABLE_NAME]='$table' | |
"@ | |
$columnsForTable = ExecuteSql $getColumnsSql | |
# Total list of columns | |
$columnNames = ColumnsAsList $columnsForTable ', ' | |
# Has this table got an identity column? | |
$hasIdentityCol = (@($columnsForTable | ? { $_.is_identity })).length -gt 0 | |
$setIdentityCols = $setIdentityCols -and $hasIdentityCol | |
# Determine the total set of columns that can physically be updated | |
$writableColumns = @($columnsForTable | | |
? { ((-not $_.is_identity) -or $setIdentityCols) ` | |
-and (-not $_.is_computed) ` | |
-and ($_.type_name -ne 'timestamp') | |
}) | |
$writableColumnNames = $writableColumns | % { $_.Column_Name }; | |
# Determine what column(s) to use to identify data | |
$keyColumns = @($columnsForTable | ? { $_.Is_Primary_Key -eq 1} | % { $_.Column_Name }) | |
if ($keyColumns.length -eq 0){ | |
throw "No PK columns detected for table $table" | |
} | |
# Not currently worrying about concurrency indicators, so all columns are concurrency columns bar the key | |
$concurrencyIndicatorColumns = @($columnsForTable | ? { $_.Is_Primary_Key -ne 1 }) | |
$concurrencyIndicatorColumnNames = ColumnsAsList $concurrencyIndicatorColumns ', '; | |
# Determine the actual set of columns that we write to. | |
# This is the writable columns less the keys | |
$columnsToUpdate = @($writableColumns | ? { ($_.Is_Primary_Key -ne 1) } | % { $_.Column_Name }) | |
if ($concurrencyIndicatorColumns.length -gt 0){ | |
$sqlExpression_ConcurrencyKeysNotEqual = [string]::join("`n`t`tOR ", ( | |
$concurrencyIndicatorColumns | % { | |
if($_.is_nullable){ | |
"(target.[{0}]<>source.[{0}]) or (target.[{0}] is null) or (source.[{0}] is null)" -f $_.column_name | |
}else{ | |
"(target.[{0}]<>source.[{0}])" -f $_.column_name | |
} | |
} )) | |
}else{ | |
$sqlExpression_ConcurrencyKeysNotEqual = '1=1' | |
} | |
$tableName = "[$schema].[$table]" | |
$tempTableName = "[$schema].[#Sync$table]" | |
$dataQuery = "select $columnNames from $tableName" | |
write-verbose $dataQuery | |
$dataForInsert = GetDataForInsert $dataQuery | |
function SetIdentityInsert($tableName, $state){ | |
if ($setIdentityCols){ | |
@" | |
SET IDENTITY_INSERT $tableName $state | |
GO | |
"@ | |
} | |
} | |
@" | |
-- powershell .\UpdateScriptFromTable.ps1 -table:$table -schema:$schema -database:$database -server:$server -setIdentityCols:$("`$$setIdentityCols".ToLower()) -noUpdate:$("`$$noUpdate".ToLower()) | |
PRINT 'Starting $tableName Syncronization' | |
GO | |
IF EXISTS(SELECT * FROM sys.tables where object_id = OBJECT_ID(N'$tempTableName')) | |
DROP TABLE $tempTableName | |
GO | |
-- Create temp table to add synch data to... | |
SELECT TOP 0 * INTO $tempTableName FROM $tableName | |
GO | |
SET NOCOUNT ON | |
$(SetIdentityInsert $tempTableName 'ON') | |
INSERT $tempTableName ($columnNames) | |
VALUES | |
---------------------------------------------------------------------- | |
-- ($columnNames) | |
$dataForInsert | |
-- Add new values here, or regenerate this script using .\UpdateScriptFromTable.ps1 | |
---------------------------------------------------------------------- | |
SET NOCOUNT OFF | |
GO | |
$(SetIdentityInsert $tempTableName 'OFF') | |
$(SetIdentityInsert $tableName 'ON') | |
MERGE $tableName AS Target | |
USING $tempTableName AS Source | |
ON ( | |
$(CreateSetter $keyColumns 'target.' 'source.' "`n`tAND ") | |
) | |
"@ | |
if((-not $noupdate) -and $columnsToUpdate){ | |
@" | |
WHEN MATCHED AND | |
$sqlExpression_ConcurrencyKeysNotEqual | |
THEN | |
UPDATE | |
SET $(CreateSetter $columnsToUpdate '' 'source.' "`n`t`t`t,") | |
"@ | |
} | |
@" | |
WHEN NOT MATCHED BY TARGET | |
THEN | |
INSERT ($(CreateColumnList $writableColumnNames)) | |
VALUES ($(CreateColumnList $writableColumnNames)) | |
"@ | |
if(-not $noupdate){ | |
@" | |
WHEN NOT MATCHED BY SOURCE THEN | |
DELETE; | |
"@ | |
} | |
@" | |
GO | |
$(SetIdentityInsert $tableName 'OFF') | |
DROP TABLE $tempTableName; | |
GO | |
PRINT 'Synchronised $tableName' | |
GO | |
"@ | |
$conn.Close(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Oh my, it's very PowerShell v1 isn't it. I'll have to update it sometime.