{"id":84400,"date":"2019-05-30T10:24:07","date_gmt":"2019-05-30T10:24:07","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84400"},"modified":"2019-05-30T10:24:07","modified_gmt":"2019-05-30T10:24:07","slug":"scripting-out-sql-server-data-as-insert-statements-via-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/scripting-out-sql-server-data-as-insert-statements-via-powershell\/","title":{"rendered":"Scripting out SQL Server Data as Insert statements via PowerShell"},"content":{"rendered":"

As a companion script with my recent article about\u00a0\u00a0Scripting out several databases on a server,<\/a>\u00a0here is a similar\u00a0 script that either scripts out the data for\u00a0 the SQL Server tables you specify\u00a0 as insert statements, or else as\u00a0 native BCP files if they are too large for insert statements.<\/p>\n

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.\u00a0 \u00a0This can then be placed in source control and\u00a0 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.\u00a0<\/p>\n

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.<\/p>\n

<# script for writing out data from one or more databases either in insert statements\r\n(useful for scripting small tables of static data) or in native BCP files.\r\nYou can use it to script the table followed by the data-insertion script if you set \r\n    'scriptSchema' = $true\r\nOther types of BCP output are easy to do by altering the BCP parameters in the \r\nscript slightly.\r\nYou can specify which tables you want to script out via a regex. You can use wildcards\r\nto specify the databases\r\n   #>\r\n\r\n$Filepath = 'PathToWhereToStoreThem' # local directory to save build-scripts to\r\n$DataSource = 'MySQLServerInstance' # server name and instance\r\n$Databases = @('A*','MyDbase','Pubs') <# the databases to copy from. wildcard comparison *, ?\r\n [a-d](range) and [and](set of chars) #>\r\n$SQLUserName = 'PhilFactor'#leave blank if Windows auth\r\n$TablesRegex = '.*' # Regex match to specify tables\r\n$UseBCP =$false;\r\n\r\n# set \"Option Explicit\" to catch subtle errors\r\nset-psdebug -strict\r\n$ErrorActionPreference = \"stop\" # you can opt to stagger on, bleeding, if an error occurs\r\n#load the sqlserver module\r\n$popVerbosity = $VerbosePreference\r\n$VerbosePreference = \"Silentlycontinue\"\r\n# the import process is very noisy if you are in verbose mode\r\nImport-Module sqlserver -DisableNameChecking #load the SQLPS functionality\r\n$VerbosePreference = $popVerbosity\r\n# get credentials if necessary\r\nif ($SQLUserName -ne '') #then it is using SQL Server Credentials\r\n{\r\n  $SqlEncryptedPasswordFile = `\r\n  \"$env:USERPROFILE\\$($SqlUserName)-$($SQLInstance).txt\"\r\n  # test to see if we know about the password in a secure string stored in the user area\r\n  if (Test-Path -path $SqlEncryptedPasswordFile -PathType leaf)\r\n  {\r\n    #has already got this set for this login so fetch it\r\n    $Sqlencrypted = Get-Content $SqlEncryptedPasswordFile | ConvertTo-SecureString\r\n    $SqlCredentials = `\r\n    New-Object System.Management.Automation.PsCredential($SqlUserName, $Sqlencrypted)\r\n  }\r\n  else #then we have to ask the user for it\r\n  {\r\n    #hasn't got this set for this login\r\n    $SqlCredentials = get-credential -Credential $SqlUserName\r\n    $SqlCredentials.Password | ConvertFrom-SecureString |\r\n    Set-Content $SqlEncryptedPasswordFile\r\n  }\r\n}\r\n\r\n$ms = 'Microsoft.SqlServer'\r\n$My = \"$ms.Management.Smo\" #\r\nif ($SQLUserName -eq '') #dead simple if using windows security\r\n{ $s = new-object (\"$My.Server\") $DataSource }\r\nelse # if using sql server security we do it via a connection object\r\n{\r\n  $ServerConnection = new-object \"$ms.Management.Common.ServerConnection\" (\r\n    $DataSource, $SQLUsername, $SqlCredentials.Password)\r\n  $s = new-object (\"$My.Server\") $ServerConnection\r\n}\r\nif ($s.Version -eq $null) { Throw \"Can't find the instance $Datasource\" }\r\n$CreationScriptOptions = new-object (\"$My.ScriptingOptions\")\r\n<# this is only needed if we are doing insert statements #>\r\n$MyPreferences = @{\r\n  'ScriptBatchTerminator' = $true; # this only goes to the file\r\n  'ToFileOnly' = $true; #no need of string output as well\r\n  'ScriptData' = $true;\r\n  'scriptSchema' = $false;\r\n  'Encoding' = [System.Text.Encoding]::UTF8;\r\n}\r\n$MyPreferences.GetEnumerator() |\r\n  Foreach{ $Name = $_.name; $CreationScriptOptions.$name = $_.Value }\r\n\r\n$possibilities = $s.Databases | select name\r\n$DatabaseList = @()\r\n$DatabaseList += $databases |\r\nwhere { $_ -Notlike '*[*?]*' } |\r\nwhere { $possibilities.Name -contains $_ }\r\n$DatabaseList += $databases |\r\n   where { $_ -like '*[*?]*' } |\r\n     foreach{ $wildcard = $_; $possibilities.Name | where { $_ -like $wildcard } }\r\n$DatabaseList | Sort-Object -Unique |\r\n  foreach {\r\n  write-verbose \"now doing $($_)\"\r\n  $TheDatabase = $s.Databases[$_]\r\n  \r\n  $TheDatabase.Tables | where { $_.IsSystemObject -eq $false -and $_.name -match $tablesRegex } |\r\n    foreach{\r\n    <# calculate where it should be saved #>\r\n    $directory = \"$($FilePath)\\$($s.Name)\\$($TheDatabase.Name)\\Data\"\r\n    <# check that the directory exists #>\r\n    if (-not (Test-Path -PathType Container $directory))\r\n    {\r\n      <# we create the  directory if it doesn't already exist #>\r\n      $null = New-Item -ItemType Directory -Force -Path $directory;\r\n    }\r\n    if ($UseBCP -eq $true) <# then we are doing a Native BCP#>\r\n    {\r\n      if ($SQLUserName -eq '')<# OK. Easy, a trusted connection #>\r\n      {\r\n        #native format -n, Trusted connection -T\r\n        BCP \"$($_.Schema).$($_.Name)\"  out  \"$($directory)\\$($_.Schema)_$($_.Name).bcp\"   `\r\n          -n -T \"-d$($TheDatabase.Name)\"  \"-S$($s.Name)\"\r\n      }\r\n      else <# if not a trusted connection we need to provide a userid and password #>\r\n      {\r\n      $progress='';\r\n        $Progress=BCP \"$($_.Schema).$($_.Name)\"  out  \"$($directory)\\$($_.Schema)_$($_.Name).bcp\"  `\r\n          -n \"-d$($TheDatabase.Name)\"  \"-S$($s.Name)\"  `\r\n          \"-U$($s.ConnectionContext.Login)\" \"-P$($s.ConnectionContext.Password)\"\r\n      }\r\n      Write-verbose \"Writing out to $($_.Schema).$($_.Name) $($directory)\\$($_.Schema)_$($_.Name).bcp\r\n      $progress\"\r\n      if (-not ($?)) # if there was an error\r\n      {\r\n        throw (\"Error with data export of $($directory)\\$($_.Schema)_$($_.Name).bcp \");\r\n      }\r\n      \r\n    }\r\n    else <# we are doing insert statements #>\r\n    {\r\n      $CreationScriptOptions.Filename =   `\r\n        \"$($FilePath)\\$($s.Name)\\$($TheDatabase.Name)\\Data\\$($_.Schema)_$($_.Name)_Data.sql\";\r\n      $scripter = new-object (\"$My.Scripter\") ($s)\r\n      $scripter.Options = $CreationScriptOptions\r\n      $scripter.EnumScript($_.Urn)\r\n    }\r\n  }\r\n}\r\n\"I have done my best to obey, Master. \"\r\n<\/pre>\n
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.\u00a0 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\u00a0 multi-statement query? Maybe one day.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
As a companion script with my recent article about\u00a0\u00a0Scripting out several databases on a server,\u00a0here is a similar\u00a0 script that either scripts out the data for\u00a0 the SQL Server tables you specify\u00a0 as insert statements, or else as\u00a0 native BCP files if they are too large for insert statements. The purpose of scripting out the……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-84400","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84400","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=84400"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84400\/revisions"}],"predecessor-version":[{"id":84402,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/84400\/revisions\/84402"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=84400"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=84400"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=84400"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=84400"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}