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

What exactly is the question? If you can change this to run as pure powershell? — Gerhard, Feb 11 '19 at 12:00
I want to use PowerShell to run the Data Seeding feature from SSMS and generate the SQL script — GoldenAge, Feb 11 '19 at 12:07
LoadWithPartialName is now [obsolete](https://learn.microsoft.com/en-us/dotnet/api/system.reflection.assembly.loadwithpartialname?view=netframework-4.7.2). The recommended solution for PowerShell V3 and up is to use the Add-Type cmdlet. — Theo, Feb 11 '19 at 12:14
Ok, I'll bear that in mind however is there an option to build similar script for the creation of the Seeding scripts for each table? — GoldenAge, Feb 11 '19 at 12:58

score 4 · Accepted Answer · answered Feb 13 '19 at 20:26

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.
}

I want to generate data seeding script only. It also generates the schema seeder script. How disable the 2nd thing? — GoldenAge, Feb 14 '19 at 09:44
ok I found in the documentation. Need to add `$tableScripter.Options.ScriptSchema = $False` — GoldenAge, Feb 14 '19 at 09:48
Your solution seems to work. I've [read](https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/installing-smo?view=sql-server-2017#system-requirements) that SMO is a NuGet package. I'm just wondering does this script require to have SSMS installed on the machine or only .net framework 4.0> to run it? — GoldenAge, Feb 14 '19 at 10:20

Generate data seeding script using PowerShell and SSMS

1 Answers1