Version: 0.14

SQL Server Examples

Comprehensive examples for transferring data from and to SQL Server databases.

MSSQL to MSSQL - Single-threaded Transfer

Simple transfer without parallelization for smaller datasets.

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_copy" `
  --query "select * from orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode Truncate

MSSQL to MSSQL - Parallel Transfer with Random Method

Transfer using Random distribution based on a key column.

Windows Authentication
SQL Authentication

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "Random" `
  --distributeKeyColumn "o_orderkey" `
  --degree 12 `
  --loadmode Truncate

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --targetuser "FastUser" `
  --targetpassword "FastPassword" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "Random" `
  --distributeKeyColumn "o_orderkey" `
  --degree 12 `
  --loadmode Truncate

MSSQL to MSSQL - Parallel Transfer with RangeId Method

Transfer using RangeId distribution for evenly distributed integer keys.

Using table name:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "RangeId" `
  --distributeKeyColumn "o_orderkey" `
  --degree 12 `
  --loadmode Truncate

Using query with filter:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --query "select * from dbo.orders WHERE o_orderdate between '19950101' and '19951231'" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_1995" `
  --parallelmethod "RangeId" `
  --distributeKeyColumn "o_orderkey" `
  --degree 12 `
  --loadmode Append

MSSQL to MSSQL - Parallel Transfer with Ntile Method

Transfer using Ntile distribution for evenly distributed chunks.

Full table transfer:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "Ntile" `
  --distributeKeyColumn "o_orderkey" `
  --degree 12 `
  --loadmode Truncate

Filtered transfer with date column:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --query "select * from dbo.orders WHERE o_orderdate between '19950101' and '19951231'" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_1995" `
  --parallelmethod "Ntile" `
  --distributeKeyColumn "o_orderdate" `
  --degree 12 `
  --loadmode Append

MSSQL to MSSQL - Parallel Transfer with DataDriven Method

Transfer using DataDriven distribution based on distinct values of a column or partition function.

Using partition function:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch10_collation_bin2" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch10_collation_bin2_copy" `
  --query "select * from orders_part" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders" `
  --parallelmethod "DataDriven" `
  --distributeKeyColumn "`$PARTITION.PF_DATE(o_orderdate)" `
  --degree 10 `
  --loadmode "Truncate"

Using year function:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --query "select * from dbo.orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "DataDriven" `
  --distributeKeyColumn "YEAR(o_orderdate)" `
  --degree 7 `
  --loadmode Append

MSSQL to MSSQL - Using Physloc Method

Transfer using physical location (should only be used on readonly/snapshot databases).

warning

The Physloc method should only be used on readonly tables, databases, or snapshots, as it relies on physical page locations that may change during writes.

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcetrusted `
  --sourcedatabase "tpch_test" `
  --sourceschema "dbo" `
  --query "select * from dbo.orders_15M" `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost,11433" `
  --targettrusted `
  --targetdatabase "tpch_test" `
  --targetschema "dbo" `
  --targettable "orders_ccsi" `
  --loadmode "Truncate" `
  --batchsize 120000 `
  --parallelmethod "Physloc" `
  --degree 10 `
  --mapmethod "Name" `
  --loadmode Truncate

MSSQL to PostgreSQL

Transfer data from SQL Server to PostgreSQL.

Without parallelization:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "pgcopy" `
  --targetserver "localhost:5432" `
  --targetdatabase "tpch_import" `
  --query "select * from dbo.orders" `
  --targetuser "FastUser" `
  --targetpassword "FastPassword" `
  --targetschema "tpch_10" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode "Truncate"

With Random method:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "pgcopy" `
  --targetserver "localhost:5432" `
  --targetdatabase "tpch_import" `
  --query "select * from dbo.orders" `
  --targetuser "FastUser" `
  --targetpassword "FastPassword" `
  --targetschema "tpch_10" `
  --targettable "orders" `
  --parallelmethod "Random" `
  --distributeKeyColumn "o_orderkey" `
  --degree 10 `
  --loadmode "Truncate"

MSSQL to MySQL

Transfer data from SQL Server to MySQL.

Without parallelization:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "mysqlbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch" `
  --query "select * from dbo.orders" `
  --targetuser "FastUser" `
  --targetpassword "FastPassword" `
  --targetschema "tpch" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode "Truncate"

With Random method:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "mysqlbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch" `
  --query "select * from dbo.orders" `
  --targetuser "FastUser" `
  --targetpassword "FastPassword" `
  --targetschema "tpch" `
  --targettable "orders" `
  --parallelmethod "Random" `
  --distributeKeyColumn "o_orderkey" `
  --degree 6 `
  --loadmode "Truncate"

With DataDriven method on partitioned table:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "mysqlbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch" `
  --query "select * from dbo.orders_part" `
  --targetuser "FastUser" `
  --targetpassword "FastPassword" `
  --targetschema "tpch" `
  --targettable "orders" `
  --parallelmethod "DataDriven" `
  --distributeKeyColumn "`$PARTITION.PF_DATE(o_orderdate)" `
  --degree 7 `
  --loadmode "Truncate"

MSSQL to DuckDB

Transfer data from SQL Server to DuckDB.

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch10_collation_bin2" `
  --sourceschema "dbo" `
  --sourcetable "orders_1M" `
  --sourcetrusted `
  --targetconnectiontype "duckdb" `
  --targetdatabase "tpch10_insert" `
  --targetschema "tpch10" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode "Truncate" `
  --targetserver "D:\OpenData\TPCH\data\10\duckdb\tpch10_insert.duckdb"

Using ODBC Connection

Transfer using ODBC DSN for connection.

Single-threaded:

.\FastTransfer.exe `
  --sourceconnectiontype "odbc" `
  --sourcedsn "DSNMsSQLTPCH" `
  --sourcedatabase "tpch_test" `
  --query "select * from dbo.orders LIMIT 1000" `
  --sourceuser "FastUser" `
  --sourcepassword "FastPassword" `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders" `
  --parallelmethod "None" `
  --loadmode "Truncate"

Dynamic Degree

Use negative degree values to automatically determine parallelism based on CPU cores.

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "Random" `
  --distributeKeyColumn "o_orderkey" `
  --degree -4 `
  --loadmode "Truncate"

info

A negative degree value (e.g., -4) multiplies the absolute value by the number of CPU cores to determine the actual degree of parallelism.

Load Modes

FastTransfer supports different load modes for the target table:

Truncate: Truncates the target table before loading
Append: Appends data to existing table (default)

Example with Append mode:

.\FastTransfer.exe `
  --sourceconnectiontype "mssql" `
  --sourceserver "localhost" `
  --sourcedatabase "tpch_test" `
  --sourcetrusted `
  --targetconnectiontype "msbulk" `
  --targetserver "localhost" `
  --targetdatabase "tpch_test" `
  --sourceschema "dbo" `
  --sourcetable "orders" `
  --targettrusted `
  --targetschema "dbo" `
  --targettable "orders_copy" `
  --parallelmethod "None" `
  --loadmode "Truncate"

tip

Build your command with the Wizard

MSSQL to MSSQL - Single-threaded Transfer​

MSSQL to MSSQL - Parallel Transfer with Random Method​

MSSQL to MSSQL - Parallel Transfer with RangeId Method​

MSSQL to MSSQL - Parallel Transfer with Ntile Method​

MSSQL to MSSQL - Parallel Transfer with DataDriven Method​

MSSQL to MSSQL - Using Physloc Method​

MSSQL to PostgreSQL​

MSSQL to MySQL​

MSSQL to DuckDB​

Using ODBC Connection​

Dynamic Degree​

Load Modes​