Invoke-SQLBulkCopy Help Needed

[kartar67]

Hi,

I am trying to use the command to copy between MySql and SQL Server in a streaming way and unable to compose the command for copying.

Can someone advise with an example of how I can copy between database systems?

[mithrandyr]

The steps are to open a connection to each database (so you will have two connections). Then to use Invoke-SqlBbulkCopy to copy data from one connection to the other. When doing the copying, you have to specify the tablename in the destination. For the source, you can either specify a table name or specify a query (with parameters). You can also specify the matching (if the columns names in the source do not match the destination).

Examples

# open connection to MySQL and name the connection 'src'
Open-MySqlConnection -Server someMySQL -Database someDB -ConnectionName src

# open connection to SQL Server and name the connection 'dst'  (the names don't matter, only that they are different, as they become the way to reference which connection to run your other commands against.
Open-SqlConnection -Server someMSSQL -Database someDB

# Example one, copying from one table to another, where the table structure is the same
Invoke-SqlBulkCopy -SourceConnectionName "src" -DestinationConnectionName "dst" -SourceTable "someTable" -DestinationTable "dbo.AnotherTable"

# Only pull some rows from the source table, this time used '-sourcequery' and '-sourceparameters'
Invoke-SqlBulkCopy -SourceConnectionName "src" -DestinationConnectionName "dst" -SourceQuery "SELECT * FROM someTable WHERE dueDate <= @dt" -SourceParameters @{dt=(Get-date)} -DestinationTable "dbo.AnotherTable"

# Pull all rows, but the column names differ between source and destination.  Per help, hashtable.key = source column name and hashtable.value = destination column name.  If a columnMap is specified, then only the columns identified in the hashtable will be copied.
Invoke-SqlBulkCopy -SourceConnectionName "src" -DestinationConnectionName "dst" -SourceTable "sometable" -DestinationTable "dbo.AnotherTable" -ColumnMap @{id="EmployeeId"; name="Name"; dob="BirthDate"; married="IsMarried" }

This should help you get started.

[kartar67]

Thank you for a quick response. It worked as expected.

[gitfvb]

With SQLServer the best solution is to use bcp. So you can write the data into that like multiple files and trigger the import then.

[mithrandyr]

@gitfvb -- Invoke-SqlBulkCopy uses System.Data.SqlClient.SqlBulkCopy class (when the destination connection is SqlServer) -- which just is the .NET equivalent of bcp.