How to create only specific delete statements using Scripter

I am using the Scripter class to give me a script for the data out of an existing database. I want to script a dataset that can be inserted into a production database. We are doing this to test if an installation of our Software is correct.

Unfortunately the dataset has to be removed later without any entries left behind so that it does not interfere with the data of our customers. So what I need are INSERT and DELTE statements. These are maintained manually at the moment which is too much of a burden.

Very well so I just went and executed the Scripter twice (once for INSERT, once for DELETE)

Problem is that when setting ScriptDrops to true then the output is in the form

DELETE FROM [dbo].[TableName]

What I would like is something of the form:

DELETE FROM [dbo].[TableName] WHERE ID = 'GUID'

Technically this would be possible since there are Primary Keys on all the tables. The Scripter class must also in some form know of that things since it also gets the order of the DELETE-statements (dependencies) correct via foreign keys.

Any help on this would be appreciated.

Following are the 2 PowerShell-scripts I am using to export the data:

ScriptRepositoryData.ps1

$scriptPath = $MyInvocation.MyCommand.Path
$scriptDirectory = Split-Path $scriptPath -Parent

. $scriptDirectory\DatabaseScripting.ps1

$filepath='c:\data.sql'
$database='ECMS_Repository'

$tablesToExclude = @(
  "SomeUnwantedTable"
  )

$tablesListFromDatabase = GetTableList $database

$tablesArray = @()

$tablesListFromDatabase |% {
  if (-not $tablesToExclude.Contains($_.Name.ToString()))
  {
    $tablesArray += $_.Name
  }
}

ScriptInsert $database $tablesArray $filepath

DatabaseScripting.ps1

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended") | out-null

Function GetTableList ($database)
{
  Invoke-SqlCmd -Database $database -query "SELECT * FROM sys.tables"
}

Function ScriptInsert ($database, $tables, $destination)
{
  try {

    $serverMO = new-object ("Microsoft.SqlServer.Management.Smo.Server") "localhost"
    if ($serverMO.Version -eq  $null) {Throw "Can't find the instance localhost"}

    $urnsToScript = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection

    $databaseMO = $serverMO.Databases.Item("ECMS_Repository")
    if ($databaseMO.Name -ne $database) {Throw "Can't find the database $database"}

    $tables |% {

      $tableListMO = $databaseMO.Tables.Item($_, "dbo")

      $tableListMO |% {
        $urnsToScript.Add($_.Urn)
      } 
    }

    $scripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') $serverMO
    $scripter.Options.ScriptSchema = $False;
    $scripter.Options.ScriptData = $true;
    $scripter.Options.ScriptDrops = $true;
    $scripter.Options.ScriptAlter = $true;

    $scripter.Options.NoCommandTerminator = $true;
    $scripter.Options.Filename = $destination;
    $scripter.Options.ToFileOnly = $true
    $scripter.Options.Encoding = [System.Text.Encoding]::UTF8

    $scripter.EnumScript($urnsToScript)

    Write-Host -ForegroundColor Green "Done"
  }
  catch {
    Write-Host
    Write-Host -ForegroundColor Red "Error occured"
    Write-Host
    Write-Host $_.Exception.ToString()
    Write-Host
  }
}

Solution

Unfortunately I did not find a way to do this using the Sql Management Objects.

Anyhow I now use the output of the Scripter and select the IDs of each table. I then use the IDs to change every line that looks like

DELETE FROM [dbo].[tableName]

to this:

DELETE FROM [dbo].[tableName] WHERE ID IN ('guid1', 'guid2')

Here is how I did it:

$content = Get-Content $destination
Clear-Content $destination

$content |% {
  $line = $_
  $table = $line.Replace("DELETE FROM [dbo].[","").Replace("]","")
  $query = "SELECT ID, ClassID FROM" + $_

  $idsAsQueryResult = Invoke-SqlCmd -Database $database -query $query
  $ids = $idsAsQueryResult | Select-Object -Expand ID

  if ($ids -ne $null) {
    $joinedIDs = [string]::Join("','",$ids)
    $newLine = $line + " WHERE ID IN ('" + $joinedIDs + "')"

    Add-Content $destination $newLine
  }
}

Where $destination is the script that has been generated with the Scripter class and $database is a string containing the database name.

I had to select a second column (ClassID which is there on all tables due to our OR mapper re-store) because of some weird error in Select-Object which I do not fully understand.

This of course only works because all tables have primary keys and all primary keys are named ID and are not combined primary keys or something. You could of course achieve the same thing for other more complicated database schemas by extracting primary key information via SQL management objects.