Use INSERT SELECT to Load Data | Native Object Store | VantageCloud Lake - Loading External Data into the Database Using INSERT ... SELECT - Teradata VantageCloud Lake

This example illustrates how a single siteno value can be stored in a persistent database table using an INSERT ... SELECT statement.

The examples use a sample river flow data set. To use your own data, replace the table and column names, and authorization object. See Variable Substitutions for Examples for the credentials and location values for the sample data set.

1. To run NOS-related commands, log on to the database as a user with the required privileges.
2. If the foreign table does not exist, create it or ask your database administrator to create it. See Setting Up to Run Examples.
3. In this example, data from a single site number is loaded into the database. Create the permanent table to store the external data:

   CREATE TABLE perm_table_name (
   columnX data_type
   ,columnY data_type
   ,columnZ data_type
   ,columnN data_type )
   PRIMARY INDEX (columnN);

4. Insert the external data into the database table:

   INSERT INTO perm_table_name
   SELECT columnX, columnY, ...columnN
   WHERE columnN = value
   FROM table_name;

Example: Loading External Data into the Database Using INSERT ... SELECT

Create the permanent table to store external data:

CREATE TABLE RiverFlowPermInsert (
SiteNo INTEGER
,Flow DECIMAL(3,2)
,GageHeight DECIMAL(3,2)
,Precipitation DECIMAL(3,2)
,datetime TIMESTAMP(0) FORMAT'Y4-MM-DDBHH:MI'
,GageHeight2 DECIMAL(3,2) )
PRIMARY INDEX (SiteNo);

If the foreign table does not exist, create it. See Setting Up to Run Examples.

Insert the external data FROM the foreign table into the permanent database table.

INSERT INTO RiverFlowPermInsert
SELECT site_no, Flow, GageHeight, Precipitation, datetime, GageHeight2
WHERE site_no = 9474000
FROM riverflow;

Query the data from the database table:

SELECT TOP 2 * FROM RiverFlowPermInsert;

Your result is similar to the following:

       SiteNo      9474000
         Flow    .80
   GageHeight   3.51
Precipitation    .00
     datetime  2018-07-26 01:00
  GageHeight2   6.66

       SiteNo      9474000
         Flow    .80
   GageHeight   3.51
Precipitation    .00
     datetime  2018-07-26 00:30
  GageHeight2   6.66

The output is displayed vertically for readability.