IMPORT FOREIGN SCHEMA

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide >

IMPORT FOREIGN SCHEMA

Previous pageReturn to chapter overviewNext page

You can import multiple foreign table or stream definitions into s-Server automatically with the IMPORT FOREIGN SCHEMA statement. This is influenced by what the foreign data wrapper supports; for example, the SYS_JDBC wrapper supports IMPORT FOREIGN SCHEMA. This wrapper is described in the topic SYS_JDBC in this guide.

Syntax

IMPORT FOREIGN SCHEMA <foreign-schema-name> <optional-limits>

  FROM SERVER <server-name>

  INTO <local_schema_name>

<foreign-schema-name> := <schema_name>

<optional-limits> :=

 [  [ LIMIT TO | EXCEPT ] ( <table-list> )  ]

 [  TABLE_NAME LIKE <string-literal>  ]

<server-name> := <identifier>

<table-list> := <table-name> { , <table-name> }...

<table-name> := <object_name>

<local-schema-name> := [ <catalog_name>  . ] <schema_name>

 

After executing IMPORT FOREIGN SCHEMA, the effect is the same as if you had executed several CREATE FOREIGN TABLE or CREATE FOREIGN STREAM commands by hand. The catalog contains the foreign table/stream definitions, and you can use them in queries.

As always for foreign tables/streams, only metadata is imported: the data remains in the foreign server, and SQLstream accesses it when you execute a query.

Example:

IMPORT FOREIGN SCHEMA Sales

     FROM SERVER OraSalesServer

     INTO OraSales;

SELECT * FROM OraSales.emp;

 

Syntax Chart

import_foreign_schema02The LIMIT TO clause restricts the import either to an explicit list of table names or to those matching a LIKE pattern. If a list is provided, all of the named tables or streams must exist on the foreign server or the import will fail. Conversely, the EXCEPT clause imports everything except those named in the list or matching the LIKE pattern. Note that the LIKE pattern is a non-standard extension.

For example, using the foreign table definition below, the import-foreign-schema command will import only table names starting with R:

CREATE FOREIGN TABLE MovieSchema.reviewers

   SERVER MovieCatalog

   OPTIONS ( directory 'review_facts', file 'reviewers.xml' )

   IMPORT FOREIGN SCHEMA MovieFacts

   LIMIT TO TABLE_NAME LIKE 'R%'

   FROM SERVER MovieCatalog

   INTO localhost.MovieSchema

 

Note that as with CREATE FOREIGN TABLE, local schema MovieSchema must already exist before the IMPORT statement is executed.

See also:

The topic CREATE FOREIGN DATA WRAPPER in this guide.
The topic CREATE SERVER in this guide.
The topic CREATE FOREIGN TABLE in this guide.
The topic CREATE FOREIGN STREAM in this guide.