IMPORT DATABASE Statement

Imports a table, view, or query from a Microsoft SQL Server, PostgreSQL, or MySQL database. The data can be copied into the notebook for fast offline access, or a live link to the remote database can be created.

Syntax

Parameters

• vendor: text
  One of the following strings: 'mssql' (Microsoft SQL Server), 'pgsql' (PostgreSQL), 'mysql' (MySQL).
  
• connection-string: text
  The ADO.NET connection string specifying the hostname, credentials, database name, and other connection details. Use the graphical import wizard via the Import menu to produce a connection string, and then copy it into your code.
• src-schema-name: text or identifier (optional)
  Applies to Microsoft SQL Server only. If provided, then this is the name of the schema in which to find the src-table-name. By default, this is dbo. When using MySQL, don't use this parameter; instead, provide the schema name as the database parameter of the connection string.
  
• src-table-name: text or identifier (optional)
  If provided, then this is the name of a remote table to be imported.
  
• sql: text (optional)
  If provided, then this is an SQL query that returns the rows to be imported. This query is in the remote server's SQL syntax. For instance, this query must be written in Transact-SQL when importing from Microsoft SQL Server.
  
• dst-table-name: text or identifier (optional)
  If provided, then the table is imported as the specified name, rather than its original name from the remote database.
  

Options

• LINK: integer (0-1, default: 0)
  This option specifies whether the remote data should be copied into the notebook.
  • 0 = Copy the data into the notebook. This is a one-time snapshot of the data and offers the fastest querying performance.
    
  • 1 = Create a live link to the remote database. This is slower, but avoids copying the data into the notebook and provides access to live data.
    
• TRUNCATE_EXISTING_TABLE: integer (0-1, default: 0)
  If the target table name exists, this option indicates whether the existing data rows should be deleted.
  • 0 = Keep existing rows and append new rows
  • 1 = Delete existing rows
• TEMPORARY_TABLE: integer (0-1, default: 0)
  If the target table name does not exist, and therefore a new table will be created, this option indicates whether the new table will be a temporary table.
  • 0 = Use CREATE TABLE
  • 1 = Use CREATE TEMPORARY TABLE

Examples

-- It's convenient to use a variable to hold the connection string.
-- The || operator is string concatenation in SQLite.
DECLARE @connectionString =
    'Data Source=localhost\SQLEXPRESS;' ||
    'Initial Catalog=Northwind;' ||
    'Integrated Security=True';

-- Import the remote table 'foo_bar' into a new table of the same
-- name in the notebook. A one-time copy of the data is performed.
IMPORT DATABASE 'mssql'
CONNECTION @connectionString
TABLE foo_bar;

-- Import a very large remote table by creating a live link. The
-- data is not copied into the notebook. Instead the remote table is
-- queried on demand.
IMPORT DATABASE 'mssql'
CONNECTION @connectionString
TABLE large_table
OPTIONS (LINK: 1);