There are a few ways to load data from one table to another. Each of them has pros and cons. This post introduces you with the simplest and fastest way to do it, the fast load.

Let’s start with “Insert Select”

The trivial way to transfer data from one table to another is the “Insert Select” option:

INSERT INTO destTable SELECT * FROM srcTable WHERE condition;

This is very simple to use, and the transferred data can also be filtered. But this is a very time consuming operation, which makes it not feasible for large tables and tables across different servers.  It also interrupts your database performance and slows it down while consuming lots of memory.

How about Bulk Copy (BCP)

BCP will definitely help transfer data of large tables. It does not overuse the database memory, and is good for big tables. Still we can do better.

SSIS

SSIS is a great solution; Everyone likes it. You have full control over your package. You decide which tables to load, and which filters to apply.

SSIS is much faster than any other option; data transfer will take from 30 to 10 percents of the time of the other transferring options.

We think it is great too. This is why we developed the fast load. It takes the SSIS “goodies”, and leaves the hassle of it behind. Here is why you want to use it:

  • The package is created for you

    Using SSIS requires crating a package describing the data-flow of the data transfer. In order to do so you need to use the special GUI, and be knowledgeable of how to use the tool. If you decide to use fast loading, we will create the package for you. You can also choose to keep it for future reuse. By calling the fast load command the package will be auto-generated.

  • Embed fast load in TSQL

    Running SSIS package is done from a program. Do you have the required coding skills? If not, fast load is the solution for you. You can embed fast load command in your TSQL and run it from your functions. No coding is required, just scripting.

  • Doing it all on the spot

    You can use data loading like any other command. No need to prepare the data-flow in advance. Use it when you need it.

Enable access to Command Shell

In order to use fast loading, the access to the command shell should be enabled in case it is disabled. This is done automatically for you, but for your information, this is what we do:

-- Allow advanced options to change
EXEC sp_configure 'show advanced options', 1
go
-- Update the currently configured value for advanced options.
RECONFIGURE
go
-- Enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
go
-- Update the currently configured value for this feature.
RECONFIGURE
go

Enabling access is done only if access was disabled. When fast loading is completed, the access is set back to its original state (by passing 0 to the sp_configure: EXEC sp_configure ‘xp_cmdshell’, 1). Note that it may happen that the access cannot be enabled. In this case the fast loading will throw an error.

Click to fast load with PdbLogic

How to use Fast load

The example below shows how to use fast load command. A database TestSourceDB contains two tables: SourceUsers  and SourceEmails. The example demonstrates the fast load of data from table SourceUsers to TargetUsers table, on the same database. SourceEmails is not loaded to the target. Note, that fast load can load data from one table to another that do not reside on the same database or on the same server.

In order to run the example below you need to download PdbLogic  and attach it to your database.

After the fast loading the script prints out all the content of the two tables, so we can see the results of the fast loading.

What if you you wish to transfer only partial table? You are not interested to have all the records in the target table, or you already have the table at the target and you just want to load the new entries. The below example shows how to use filters. As in the previous example we load data from SourceUsers to TargetUsers but only records that meet the filter of id larger than 500. Note that in this example the tables reside on different databases:

Extract and load is also a great options to transfer data in a fast manner. If no filtering is required, you should definitely consider it.

For your reference, below is the list of parameters to be passed to PdbfastLoadTable:

Parameter Description Mandatory
TargetDatabaseName Name of target database Yes
TargetSchemaName Name of target schema No
TargetObjectName Name of target table Yes
SourceDatabaseName Name of source database No
SourceSchemaName Name of source schema No
SourceObjectName Name of source table Yes
TargetLinkedServerName Name of target linked server No
SourceLinkedServerName Name of source linked server No
UserName User name on target server (if not passed, use windows credentials) No
Password Password on target server (if not passed, use windows credentials) No
SourceUserName User name on source server (if not passed, use windows credentials) No
SourcePassword Password on source server (if not passed, use windows credentials) No
LocalPath Path of the file that is automatically generated (If not passed, file will be saved on local machine c:\Shared) No
LocalFile Name of the file that is automatically generated (Default name is Pdb__ No
WhereClause Option to pass a filtering option for the data transfer (If not passed, transfer all) No
OrderByColumns Name of column by which to sort the entries in the target table No
TopRows Number of entries to load to target from the top of the sorted table (This parameter is augmenting to OrderByColumns) No
EngineThreads Maximum number of thread to run by SSIS (Default is 10) No
DefaultBufferMaxRows Default number of rows in the buffer. Its default value is 10,000 No
DefaultBufferSize Maximum size of the buffer. The default value is 10MB No
IsIgnoreIdentity If true, ignore identitiy in loading and let them be generated as the target. Default is false No
IsFireTriggers If true, fire triggers while loading. Default is false. No
IsCheckConstraints If true, check constaints while loading. Default is true. No
IsDeleteFile If true, delete XML file that was generated. In case of an error, the file is kept. Default is true. No
IsKeepNulls If true, keep the null values in the target table. If not, use the defaults. No

To use fast loading and other PartitionDB utilities, download our PdbLogic and attach to your database. .
To learn more about PartitionDB capabilities check out our blog.