| Author | Simone Bizzotto (@niphlod) |
| Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Copy-DbaDbTableData on GitHub.
Want to see the Bill Of Health for this command? Check out Copy-DbaDbTableData.
Streams table data between SQL Server instances using high-performance bulk copy operations.
Copies data between SQL Server tables using SQL Bulk Copy for maximum performance and minimal memory usage.
Unlike Invoke-DbaQuery and Write-DbaDbTableData which buffer entire table contents in memory, this function streams data directly from source to destination.
This approach prevents memory exhaustion when copying large tables and provides the fastest data transfer method available.
Supports copying between different servers, databases, and schemas while preserving data integrity options like identity values, constraints, and triggers.
Can automatically create destination tables based on source table structure, making it ideal for data migration, ETL processes, and table replication tasks.
Copy-DbaDbTableData
[[-SqlInstance] <DbaInstanceParameter>]
[[-SqlCredential] <PSCredential>]
[[-Destination] <DbaInstanceParameter[]>]
[[-DestinationSqlCredential] <PSCredential>]
[[-Database] <String>]
[[-DestinationDatabase] <String>]
[[-Table] <String[]>]
[[-View] <String[]>]
[[-Query] <String>]
[-AutoCreateTable]
[[-BatchSize] <Int32>]
[[-NotifyAfter] <Int32>]
[[-DestinationTable] <String>]
[-NoTableLock]
[-CheckConstraints]
[-FireTriggers]
[-KeepIdentity]
[-KeepNulls]
[-Truncate]
[[-BulkCopyTimeout] <Int32>]
[[-CommandTimeout] <Int32>]
[-UseDefaultFileGroup]
[[-InputObject] <TableViewBase[]>]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Copy-DbaDbTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table dbo.test_table
Copies all the data from table dbo.test_table (2-part name) in database dbatools_from on sql1 to table test_table in database dbatools_from on sql2.
PS C:\> Copy-DbaDbTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -DestinationDatabase dbatools_dest -Table [Schema].[test table]
Copies all the data from table [Schema].[test table] (2-part name) in database dbatools_from on sql1 to table [Schema].[test table] in database dbatools_dest on sql2
PS C:\> Get-DbaDbTable -SqlInstance sql1 -Database tempdb -Table tb1, tb2 | Copy-DbaDbTableData -DestinationTable tb3
Copies all data from tables tb1 and tb2 in tempdb on sql1 to tb3 in tempdb on sql1
PS C:\> Get-DbaDbTable -SqlInstance sql1 -Database tempdb -Table tb1, tb2 | Copy-DbaDbTableData -Destination sql2
Copies data from tb1 and tb2 in tempdb on sql1 to the same table in tempdb on sql2
PS C:\> Copy-DbaDbTableData -SqlInstance sql1 -Destination sql2 -Database dbatools_from -Table test_table -KeepIdentity -Truncate
Copies all the data in table test_table from sql1 to sql2, using the database dbatools_from, keeping identity columns and truncating the destination
PS C:\> $params = @{
>> SqlInstance = 'sql1'
>> Destination = 'sql2'
>> Database = 'dbatools_from'
>> DestinationDatabase = 'dbatools_dest'
>> Table = '[Schema].[Table]'
>> DestinationTable = '[dbo].[Table.Copy]'
>> KeepIdentity = $true
>> KeepNulls = $true
>> Truncate = $true
>> BatchSize = 10000
>> }
>>
PS C:\> Copy-DbaDbTableData @params
Copies all the data from table [Schema].[Table] (2-part name) in database dbatools_from on sql1 to table [dbo].[Table.Copy] in database dbatools_dest on sql2
Keeps identity columns and Nulls, truncates the destination and processes in BatchSize of 10000.
PS C:\> $params = @{
>> SqlInstance = 'server1'
>> Destination = 'server1'
>> Database = 'AdventureWorks2017'
>> DestinationDatabase = 'AdventureWorks2017'
>> DestinationTable = '[AdventureWorks2017].[Person].[EmailPromotion]'
>> BatchSize = 10000
>> Table = '[OtherDb].[Person].[Person]'
>> Query = "SELECT * FROM [OtherDb].[Person].[Person] where EmailPromotion = 1"
>> }
>>
PS C:\> Copy-DbaDbTableData @params
Copies data returned from the query on server1 into the AdventureWorks2017 on server1, using a 3-part name for the DestinationTable parameter. Copy is processed in BatchSize of 10000 rows.
See the Query param documentation for more details.
PS C:\> Copy-DbaDbTableData -SqlInstance sql1 -Database tempdb -View [tempdb].[dbo].[vw1] -DestinationTable [SampleDb].[SampleSchema].[SampleTable] -AutoCreateTable
Copies all data from [tempdb].[dbo].[vw1] (3-part name) view on instance sql1 to an auto-created table [SampleDb].[SampleSchema].[SampleTable] on instance sql1
Source SQL Server.You must have sysadmin access and server version must be SQL Server version 2000 or greater.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Login to the source instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Target SQL Server instance where table data will be copied to. Accepts one or more SQL Server instances.
Specify this when copying data to a different server than the source, or when doing cross-instance data transfers.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Alternative credentials for authenticating to the destination instance. Required when your current Windows credentials don't have access to the target server.
Use this for cross-domain scenarios, SQL authentication, or when the destination requires different security context than the source.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Source database containing the table or view to copy data from. Required when not using pipeline input.
Must exist on the source instance and your account must have read permissions on the specified objects.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Target database where copied data will be inserted. Defaults to the same database name as the source.
Use this when copying data to a different database name on the destination instance or for cross-database copies within the same server.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Source table name to copy data from. Accepts 2-part ([schema].[table]) or 3-part ([database].[schema].[table]) names.
Use square brackets for names with spaces or special characters. Cannot be used simultaneously with the View parameter.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Source view name to copy data from. Accepts 2-part ([schema].[view]) or 3-part ([database].[schema].[view]) names.
Use square brackets for names with spaces or special characters. Cannot be used simultaneously with the Table parameter.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Custom SQL SELECT query to use as the data source instead of copying the entire table or view. Supports 3 or 4-part object names.
Use this when you need to filter rows, join multiple tables, or transform data during the copy operation. Still requires specifying a Table or View parameter for metadata purposes.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Automatically creates the destination table if it doesn't exist, using the same structure as the source table.
Essential for initial data migrations or when copying to new environments where destination tables haven't been created yet.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Number of rows to process in each bulk copy batch. Defaults to 50000 rows.
Reduce this value for memory-constrained systems or increase it for faster transfers when copying large tables with sufficient memory.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 50000 |
Number of rows to process before displaying progress updates. Defaults to 5000 rows.
Set to a lower value for frequent updates on small tables or higher for less verbose output on large table copies.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 5000 |
Target table name where data will be inserted. Defaults to the same name as the source table.
Use this when copying to a table with a different name or schema, or when specifying 3-part names for cross-database operations.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
Disables the default table lock (TABLOCK) on the destination table during bulk copy operations.
Use this when you need to allow concurrent read access to the destination table, though it may reduce bulk copy performance.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Enables constraint checking during bulk copy operations. By default, constraints are ignored for performance.
Use this when data integrity validation is more important than copy speed, particularly when copying from untrusted sources.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Enables INSERT triggers to fire during bulk copy operations. By default, triggers are bypassed for performance.
Use this when you need audit trails, logging, or other trigger-based business logic to execute during the data copy.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Preserves the original identity column values from the source table. By default, the destination generates new identity values.
Essential when copying reference tables or when you need to maintain exact ID relationships across systems.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Preserves NULL values from the source data instead of replacing them with destination column defaults.
Use this when you need exact source data reproduction, especially when NULL has specific business meaning versus default values.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Removes all existing data from the destination table before copying new data. Prompts for confirmation unless -Force is used.
Essential for refresh scenarios where you want to replace all destination data with current source data.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Maximum time in seconds to wait for bulk copy operations to complete. Defaults to 5000 seconds (83 minutes).
Increase this value when copying very large tables that may take longer than the default timeout period.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 5000 |
Maximum time in seconds to wait for the source query execution before timing out. Defaults to 0 (no timeout).
Set this when querying large tables or complex views that may take longer to read than typical query timeouts allow.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | 0 |
Creates new tables using the destination database's default filegroup instead of matching the source table's filegroup name.
Use this when the destination database has different filegroup configurations or when you want all copied tables in the PRIMARY filegroup.
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Accepts table or view objects from Get-DbaDbTable or Get-DbaDbView for pipeline operations.
Use this to copy multiple tables efficiently by piping them from discovery commands.
| Alias | |
| Required | False |
| Pipeline | true (ByValue) |
| Default Value |
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 |
If this switch is enabled, no actions are performed but informational messages will be displayed that explain what would happen if the command were to run.
| Alias | wi |
| Required | False |
| Pipeline | false |
| Default Value |
If this switch is enabled, you will be prompted for confirmation before executing any operations that change state.
| Alias | cf |
| Required | False |
| Pipeline | false |
| Default Value |