Generates a reference data insert/update script, based on contents of an existing table

UpdateScriptFromTable.ps1

<#
.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();

Oh my, it's very PowerShell v1 isn't it. I'll have to update it sometime.