Union

Union Script is designed for merging several data tables with the same structures in order to get a new table that is the sum of all the component tables. It works in a similar way as a standard import and export, however it requires 3 files to be created (all of them should have the same names – e.g. my_union.script, my_union.destination, my_union.union ):

*.script – contains information about a source and destination for imported data.

<?xml version="1.0"?> 
<Script xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> 
  <Actions> 
    <Action xsi:type="Load">
      <Source>D:\my_union.union</Source> 
      <Destination>union_table</Destination> 
    </Action> 
  </Actions> 
</Script>

*.destination – this file contains information about the destination of the union. In majority of cases this will be the database connection string.

user id=USER;password=PASSWORD;data source=dbserver\sqlexpress;integrated security=False;pooling=true;enlist=false;initial catalog=statmap;cartridge=SqlServer;schema=dbo

*.union – contains information about data sources (where the data should be imported from)

<?xml version="1.0" encoding="utf-8"?>
<Union>
  <ConnectionStrings>
    <string>data source=D:\;table name=table1;cartridge=ESRI Shapefile</string>
    <string>data source=D:\;table name=table2;cartridge=MapInfo Tab</string>
    <string>user id=sql;password=password;data source=dbserver\sqlexpress;table name=table3;cartridge=SqlServer;schema=dbo</string>
    <string>user id=or;password=password;data source=dbserver/xe;table name=table4;cartridge=Oracle;schema=or</string>
    <string>user id=pg;password=password;host=dbserver;database=EarthlightDB;table name=table5;cartridge=PostgreSql;schema=public</string>
  </ConnectionStrings>
</Union>

Please note that each data source must have the same schema.

data source – any admissible data format imported by Data Pump,
schema – means schema of a table or other source of data. It describes what columns (names and types) must be created for the data to be imported from that source.

Simple example showing usage of the Union Script.

Let’s suppose that Table1 is stored as ESRI Shape file and Table2 is saved in SQL Server database. Now modified script looks like this.

<?xml version="1.0" encoding="utf-8"?>
<Union>
  <ConnectionStrings>
    <string>data source=D:\;table name=table1;cartridge=ESRI Shapefile</string>
    <string>user id=sql;password=password;data source=dbserver\sqlexpress;table name=table2;cartridge=SqlServer;schema=dbo</string>
  </ConnectionStrings>
</Union>