SQL/MP to SQL/MX Database and Application Migration Guide

Converting SQL/MP Tables to SQL/MX Tables
HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001
5-22
Using INSERT-SELECT to Copy Data
Using INSERT-SELECT to Copy Data
Because NonStop SQL/MX supports DML statements that access both SQL/MP and
SQL/MX tables, you can use a simple INSERT-SELECT statement to copy data from a
SQL/MP table into a SQL/MX table.
Example of Using INSERT-SELECT to Copy Data
This example copies the contents of the SQL/MP table MPCAT.SALES.CUSTOMER
into the SQL/MX table MXCAT.SALES.CUSTOMER:
INSERT INTO mxcat.sales.customer
(custnum, custname, street, city, state, postcode)
SELECT custnum, custname, street, city, state, postcode
FROM mpcat.sales.customer FOR READ UNCOMMITTED;
The FOR READ UNCOMMITTED access option allows the transaction to access
locked data, which might be suitable for testing. For a higher level of data consistency,
use the FOR REPEATABLE READ access option.
One drawback of using INSERT-SELECT is that the data is inserted in a single
transaction. If the table is large, this operation might fail because of TMF transaction
length or size limitations. You can work around these limitations by performing multiple
inserts, each accessing a separate key range in the SQL/MP table. For more
information, see Loading Data Into Partitioned Tables
on page 5-22.
Loading Data Into Partitioned Tables
For range-partitioned tables, the ranges of the partitions are known in advance.
Therefore, you can separate the data on those range boundaries and quickly load
separate, sorted, parallel streams of data into each partition.
However, for hash-partitioned tables, the partitioning algorithm is internal to NonStop
SQL/MX. Before loading data into a hash-partitioned table, use the HASHPARTFUNC
function to determine the partition into which each row will be loaded. After you know
how each row will be partitioned, you can separate the data and load parallel streams
of data into each partition. For more information about HASHPARTFUNC, see the
SQL/MX Installation and Management Guide and the SQL/MX Reference Manual.
Using DataLoader/MX to Copy Data
DataLoader/MX is a software tool that loads data into SQL/MP and SQL/MX databases
and that supports single or multiple data sources and parallel loading of large
databases. DataLoader/MX works with the
import utility, which imports data from an
input file in ASCII or UCS2 format into a SQL/MX table.