Created
May 6, 2026 14:31
-
-
Save QuadmanSWE/632ccef3b375fcd0d91afd6cb2d7e3d1 to your computer and use it in GitHub Desktop.
An example of inserting batches of data using table valued parameters through a straight sql query
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Import-Module dbatools.library | |
| Import-Module dbatools | |
| # A dbatools implementation of using tvp to insert large amounts of data to SQL Server, using PowerShell as the client language. | |
| # Inspired by https://timdeschryver.dev/blog/faster-sql-bulk-inserts-with-csharp | |
| # Local environment variables for this experiment | |
| $password = Read-Host -Prompt "Enter sysadmin(sa) password for local SQL Server instance" -AsSecureString | |
| $instance = Connect-DbaInstance -SqlInstance '127.0.0.1,1433' -SqlCredential (New-Object System.Management.Automation.PSCredential('sa', $password)) | |
| $databaseName = 'mydatabase' | |
| # Setting these as default parameters for dbatools so we don't have to repeat a bunch of parameters over and over. | |
| $PSDefaultParameterValues["*-Dba*:SqlInstance"] = $instance | |
| $PSDefaultParameterValues["*-Dba*:Database"] = $databaseName | |
| # Show our target database, to make sure it exists. | |
| Get-DbaDatabase | |
| # Create our testing schema where our objects will live. Allowing us to easily clean up by just dropping the schema if we want to rerun the experiment. | |
| Invoke-DbaQuery -Query "CREATE SCHEMA test;" -ErrorAction Ignore | |
| # Show if table exists already | |
| Get-DbaDbTable -Schema 'test' | |
| #Region Create Table | |
| $targetTableCreationScript = @" | |
| DROP TABLE IF EXISTS [test].[tbl]; | |
| CREATE TABLE [test].[tbl]( | |
| [id] [int] NOT NULL, | |
| [textx] [nvarchar](256) NULL, | |
| [texty] [nvarchar](256) NULL, | |
| PRIMARY KEY CLUSTERED | |
| ( | |
| [id] ASC | |
| ) | |
| ); | |
| "@ | |
| Invoke-DbaQuery -Query $targetTableCreationScript | |
| #EndRegion | |
| #Region Create TVP | |
| $tvpCreationScript = @" | |
| -- Create the data type | |
| DROP TYPE IF EXISTS test.testdata; | |
| CREATE TYPE test.testdata AS TABLE | |
| ( | |
| [id] [int] NOT NULL, | |
| [textx] [nvarchar](256) NULL, | |
| [texty] [nvarchar](256) NULL, | |
| PRIMARY KEY CLUSTERED | |
| ( | |
| [id] ASC | |
| ) | |
| ); | |
| "@ | |
| Invoke-DbaQuery -Query $tvpCreationScript | |
| #EndRegion | |
| #Region Command to run | |
| $cmdText = @" | |
| INSERT INTO test.tbl (id, textx, texty) | |
| SELECT id, textx, texty | |
| FROM @tvpData; | |
| "@ | |
| $rowCount = 1000 * 1000 | |
| $batchSize = 5 * 10 * 1000 | |
| #Endregion | |
| ## passes DataTable as the structured TVP — equivalent to C#'s SqlParameter with SqlDbType.Structured | |
| Measure-Command { | |
| Invoke-DbaQuery -Query "TRUNCATE TABLE test.tbl" | |
| for ($i = 0; $i -lt $rowCount; $i += $batchSize) { | |
| Write-Host "Inserting rows $i to $(($i + $batchSize - 1))..." | |
| # Setup variable as DataTable to pass as TVP parameter | |
| $dataTable = [System.Data.DataTable]::new() | |
| $dataTable.Columns.Add("id", [int]) | Out-Null | |
| $dataTable.Columns.Add("textx", [string]) | Out-Null | |
| $dataTable.Columns.Add("texty", [string]) | Out-Null | |
| # Insert next batch of rows | |
| $i..($i + $batchSize - 1) | ForEach-Object { | |
| $dataTable.Rows.Add($_, "textx$_", "texty$_") | Out-Null | |
| } | |
| # Execute against sql server | |
| $p1 = New-DbaSqlParameter -ParameterName "@tvpData" -Value $dataTable -TypeName "test.testdata" -SqlDbType Structured | |
| Invoke-DbaQuery -Query $cmdText -SqlParameters $p1 | |
| } | |
| Invoke-DbaQuery -Query "SELECT COUNT(*) FROM test.tbl" -As SingleValue | Write-Host | |
| # cleans up $p1 and $dataTable to free memory, and allow for reruns | |
| Remove-Variable p1 | |
| Remove-Variable dataTable | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment