IMPORT FOREIGN SCHEMA

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

The 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: