Using your schema in MS SQL

You follow an example of how to create an external table in MS SQL using your own schema.

  1. Using MS SQL, create an external table based on a user-defined schema.
    CREATE DATABASE world;
    USE world;
    
    CREATE SCHEMA bob;
    CREATE TABLE bob.country
    (
        id   int,
        name varchar(20)
    );
    
    insert into bob.country
    values (1, 'India');
    insert into bob.country
    values (2, 'Russia');
    insert into bob.country
    values (3, 'USA');
    
    CREATE SCHEMA alice;
    CREATE TABLE alice.country
    (
        id   int,
        name varchar(20)
    );
    
    insert into alice.country
    values (4, 'Italy');
    insert into alice.country
    values (5, 'Greece');
    insert into alice.country
    values (6, 'China');
    insert into alice.country
    values (7, 'Japan');
    
  2. In MS SQL, create a user and associate them with a default schema.
    CREATE LOGIN greg WITH PASSWORD = 'GregPass123!$';
    CREATE USER greg FOR LOGIN greg WITH DEFAULT_SCHEMA=bob;
  3. Allow the user to connect to the database and run queries. For example:
    GRANT CONNECT, SELECT TO greg;