IMPORT

Once a data-source is declared, data can be imported via the IMPORT command.

table_name_in_targitdb – defines the table the import will store the data in datasource_name is the alias for one datasource table_name is the name of the table to read in the source database.

Syntax:

IMPORT table_name_in_targitdb=datasource_name.table_name

Example:

/*This imports the categories table from datasource a1 and stores it as the categories table in designated .TARGITDB file*/
IMPORT categories=a1.categories 
/*You can use Square Brackets [] around the destination table name to save it as is. You can also use CURLY {} brackets, after the . to read from an arbitrary SQL statement*/
IMPORT [Order Details]=a1.{select * from [Order Details]} 
/*You can also import data using the sql contained in another file by putting quotes around the filename. It is assumed the .sql file is in the same folder.*/ 
IMPORT sometable=a1.'Name_Of_File_to_read.sql'

IMPORT UNION ALL

Multiple UNION ALL operators can also be combined. The number of columns and field datatypes need to match across each table/ query in the Union.

By default the TARGIT InMemory Database insists on strict datatype matches between datasets in a Union All.

For instance, numeric fieldA (which is, say, a Double) in Table1 linking with fieldA (which is datatype Float) in Table2 will cause an error as the InMemory Server expects them to have the same datatype. This assumption improves performance, but may be switched off by use of the USETEMP keyword.

Note: SQL syntax is dependent on the source database syntax rules. For a list of specific SQL Syntax see TARGIT InMemory Database SQL Reference.

Syntax:

IMPORT combined_table=USETEMP A1.table1 UNION ALL a2.table2

Example:

/*This creates a temporary table behind the scenes in TARGIT InMemory Database for both tables before creating the final one. Thus more time and overheads are used.*/ 
IMPORT decimal_test= source2.decimal_test 
IMPORT Products= source2.products 
IMPORT large_query = source1. 
{SELECT Orders.OrderID AS int_col, 
Orders.OrderDate AS date_col, 
[Order Details].Quantity AS short_col, 
[Order Details].UnitPrice AS dec_col, 
Orders.ShipName AS text_col, 
Products.Discontinued AS bit_col, 
CDbl([Order Details].[UnitPrice]) AS Dbl_Col, 
CSng([Order Details].[UnitPrice]) AS float_col, 
Categories.Picture AS bitarray_col 
FROM Categories 
INNER JOIN (Products INNER JOIN (Orders INNER JOIN 
[Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Categories.CategoryID = Products.CategoryID; } 
import datatype_table= me.{ 
select *, CAST_NUM_AS_LONG(int_col) as int64_col, 
CAST_NUM_AS_BYTE(int_col%64) as byte_col 
from large_query }

/* Perhaps there are Customers in two data sources (duplicate tables/ different set of customers in each) and you need to bring them together as one in TARGITDB... */ 
IMPORT Combined_Custs =USETEMP Source1.customers 
UNION ALL Source3.customers 

/* Alternatively, you could bring in both, and then combine...*/ 
IMPORT Custs1 =Source1.customers 
IMPORT Custs2 =Source3.{SELECT * FROM customers} 
IMPORT Combined_Custs = ME.Custs1 UNION ALL ME.Custs2