Generate data seeding script using PowerShell and SSMS

Question

Here I found a solution for the manual creation of the data seeding script. The manual solution allows me to select for which tables I want to generate the inserts

I would like to know if there is an option to run the same process via PowerShell?

So far I have managed how to create a SQL script which creates the Database schema seeder:

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

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "(localdb)\mssqlLocalDb"  

$dbs=$s.Databases 

#$dbs["HdaRoot"].Script() 
$dbs["HdaRoot"].Script() | Out-File C:\sql-seeding\HdaRoot.sql  

#Generate script for all tables

foreach ($tables in $dbs["HdaRoot"].Tables) 
{
    $tables.Script() + "`r GO `r " | out-File C:\sql-seeding\HdaRoot.sql  -Append
} 

however is there any similar way to generate the data seeding script?

Any ideas? Cheers

Answer 1

You can use the SMO scripter class. This will allow you to script the table creates as well as INSERT statements for the data within the tables.

In my example I'm directly targeting TempDB and defining an array of table names I want to script out rather than scripting out every table.

Scripter has a lot of options available, so I've only done a handful in this example - the important one for this task is Options.ScriptData. Without it you'll just get the schema scripts that you're already getting.

The EnumScript method at the end does the actual work of generating the scripts, outputting, and appending the script to the file designated in the options.

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

## target file
$outfile = 'f:\scriptOutput.sql' 

## target server
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "localhost"  

## target database
$db = $s.databases['tempdb'] 

## array of tables that we want to check
$tables = @('Client','mytable','tablesHolding')

## new Scripter object
$tableScripter = new-object ('Microsoft.SqlServer.Management.Smo.Scripter')($s) 

##define options for the scripter
$tableScripter.Options.AppendToFile = $True
$tableScripter.Options.AllowSystemObjects = $False
$tableScripter.Options.ClusteredIndexes = $True
$tableScripter.Options.Indexes = $True
$tableScripter.Options.ScriptData = $True
$tableScripter.Options.ToFileOnly = $True
$tableScripter.Options.filename = $outfile

## build out the script for each table we defined earlier
foreach ($table in $tables) 
{
    $tableScripter.enumscript(@($db.tables[$table])) #enumscript expects an array. this is ugly, but it gives it what it wants.
}