Scripting out SQL Server Data as Insert statements via PowerShell

As a companion script with my recent article about  Scripting out several databases on a server, here is a similar  script that either scripts out the data for  the SQL Server tables you specify  as insert statements, or else as  native BCP files if they are too large for insert statements.

The purpose of scripting out the insert statements is that you can create a table that is designed for static data and fill it with the required data in the same script.   This can then be placed in source control and  the table will be filled with the data on build. Because this is only realistic with small tables, I’ve added the option to use native BCP. This is faster and takes between a quarter and a fifth of the disk space. 

I use wildcards to specify the databases because they are more intuitive for Ops people than RegExes, but obtusely, I use a Regex for the specification of the tables. This is because you can’t specify a list of alternatives in a Wildcard whereas it is easy with a RegEx.

I always think, each time I script a way of getting data from SQL Server, that it will be my last, but there always seems to be a new requirement that pops up. This time, it was for a script that would produce not only the table build statement but also the data insert statement. It is possible to do this in SSMS, though if you need to do more than a couple of tables it can get boring. It is possible to script any routine operation that you perform in SSMS because both PowerShell and SSMS use the SMO library to achieve their ends.  I personally would prefer to use a multi-statement VALUES derived table to insert data, because it is faster and more versatile. What? You want me to provide a routine that scripts out a table as a  multi-statement query? Maybe one day.