Scripting out SQL Server Data as Insert statements via PowerShell

Comments 0

Share to social media

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.

 

Load comments

About the author

Phil Factor

See Profile

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 40 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

Phil Factor's contributions