Author | Chrissy LeMaire (@cl), netnerds.net |
Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Export-DbaDbTableData on GitHub.
Want to see the Bill Of Health for this command? Check out Export-DbaDbTableData.
Generates INSERT statements from table data for migration and deployment scripts
Creates executable INSERT statements from existing table data, making it easy to move data between SQL Server instances or environments. This is particularly useful for migrating reference tables, lookup data, or configuration tables where you need the actual data values rather than just the table structure. The generated scripts include proper USE database context and can be saved to files or piped to other commands for further processing.
Export-DbaDbTableData
[-InputObject] <Table[]>
[[-Path] <String>]
[[-FilePath] <String>]
[[-Encoding] <String>]
[[-BatchSeparator] <String>]
[-NoPrefix]
[-Passthru]
[-NoClobber]
[-Append]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Get-DbaDbTable -SqlInstance sql2017 -Database AdventureWorks2014 -Table EmployeePayHistory | Export-DbaDbTableData
Exports data from EmployeePayHistory in AdventureWorks2014 in sql2017
PS C:\> Get-DbaDbTable -SqlInstance sql2017 -Database AdventureWorks2014 -Table EmployeePayHistory | Export-DbaDbTableData -FilePath C:\temp\export.sql -Append
Exports data from EmployeePayHistory in AdventureWorks2014 in sql2017 using a trusted connection - Will append the output to the file C:\temp\export.sql if it already exists
Script does not include Batch Separator and will not compile
PS C:\> Get-DbaDbTable -SqlInstance sql2016 -Database MyDatabase -Table 'dbo.Table1', 'dbo.Table2' -SqlCredential sqladmin | Export-DbaDbTableData -FilePath C:\temp\export.sql -Append
Exports only data from 'dbo.Table1' and 'dbo.Table2' in MyDatabase to C:\temp\export.sql and uses the SQL login "sqladmin" to login to sql2016
Accepts table objects from Get-DbaDbTable through the pipeline.
Use this to process specific tables you've already identified rather than specifying table names again.
Alias | |
Required | True |
Pipeline | true (ByValue) |
Default Value |
Sets the directory where output files will be created when not using FilePath.
Defaults to the dbatools export directory configured in module settings.
Alias | |
Required | False |
Pipeline | false |
Default Value | (Get-DbatoolsConfigValue -FullName 'Path.DbatoolsExport') |
Specifies the complete path and filename for the output SQL script.
Use this when you need the INSERT statements saved to a specific file location for deployment or version control.
Alias | OutFile,FileName |
Required | False |
Pipeline | false |
Default Value |
Controls the character encoding of the exported SQL file. Defaults to UTF8.
Use UTF8 for compatibility with most modern SQL tools, or ASCII for older systems that don't support Unicode.
Valid values are:
Alias | |
Required | False |
Pipeline | false |
Default Value | UTF8 |
Accepted Values | ASCII,BigEndianUnicode,Byte,String,Unicode,UTF7,UTF8,Unknown |
Adds batch separators (like GO) between INSERT statements in the output script.
Use this when creating deployment scripts that will be executed in SQL Server Management Studio or sqlcmd.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Excludes the USE database statement and other prefixes from the generated script.
Use this when combining output with other scripts or when the database context is already established.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Displays the generated INSERT statements in the PowerShell console in addition to file output.
Useful for reviewing the script content before execution or when piping to other commands.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Prevents overwriting existing files at the specified FilePath.
Use this as a safety measure to avoid accidentally replacing important deployment scripts.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Adds the INSERT statements to the end of an existing file instead of creating a new one.
Useful when building comprehensive deployment scripts from multiple table exports or combining with other SQL operations.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Shows what would happen if the command were to run. No actions are actually performed
Alias | wi |
Required | False |
Pipeline | false |
Default Value |
Prompts you for confirmation before executing any changing operations within the command
Alias | cf |
Required | False |
Pipeline | false |
Default Value |