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 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
-- Update the currently configured value for advanced options.
-- Enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- Update the currently configured value for this feature.
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.
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.
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:
|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|