Insert Data

This document describes how to insert data into MatrixOne by using the SQL language.

Before you start

Make sure you have already Deployed standalone MatrixOne.

INSERT INTO Statement

It is possible to write the INSERT INTO statement in several ways:

1. Specify both the column names and the values to be inserted:

   INSERT INTO tbl_name (a,b,c) VALUES (1,2,3);

2. If you add values for all the table columns, you do not need to specify the column names in the SQL query. However, make sure the values’ order is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:

   INSERT INTO tbl_name VALUES (1,2,3);

3. INSERTstatements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of comma-separated column values, with lists enclosed within parentheses and separated by commas. Example:

   INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

CREATE TABLE Customers (
  CustomerID INT AUTO_INCREMENT NOT NULL,
  CustomerName VARCHAR(40) NOT NULL,
  ContactName VARCHAR(30) NULL,
  Address VARCHAR(60) NULL,
  City VARCHAR(15) NULL,
  PostalCode VARCHAR(10) NULL,
  Country VARCHAR(15) NULL,
  PRIMARY KEY (CustomerID)
  );

INSERT INTO Example

The following SQL statement inserts a new record in the “Customers” table:

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

The selection from the “Customers” table will now look like this:

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

Example

The following SQL statement will insert a new record, but only insert data in the “CustomerName”, “City”, and “Country” columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

The selection from the “Customers” table will now look like this:

INSERT INTO…SELECT

With INSERT INTO SELECT, you can quickly insert many rows into a table from the result of a SELECT statement, which can select from one or many tables. The INSERT INTO SELECT statement requires that the data types in source and target tables match.

INSERT INTO SELECT Syntax

Copy all columns from one table to another table:

INSERT INTO *table2*
SELECT * FROM *table1
*WHERE *condition*;

Copy only some columns from one table into another table:

INSERT INTO *table2* (*column1*, *column2*, *column3*, ...)
SELECT *column1*, *column2*, *column3*, ...
FROM *table1*
WHERE *condition*;

Northwind sample database

In this tutorial we will use the Northwind sample database.

CREATE TABLE Customers (
  CustomerID INT AUTO_INCREMENT NOT NULL,
  CustomerName VARCHAR(40) NOT NULL,
  ContactName VARCHAR(30) NULL,
  Address VARCHAR(60) NULL,
  City VARCHAR(15) NULL,
  PostalCode VARCHAR(10) NULL,
  Country VARCHAR(15) NULL,
  PRIMARY KEY (CustomerID)
  );
CREATE TABLE Suppliers (
  SupplierID INT AUTO_INCREMENT NOT NULL,
  SupplierName VARCHAR(40) NOT NULL,
  ContactName VARCHAR(30) NULL,
  Address VARCHAR(60) NULL,
  City VARCHAR(15) NULL,
  PostalCode VARCHAR(10) NULL,
  Country VARCHAR(15) NULL,
  PRIMARY KEY (SupplierID)
  );

Below is a selection from the “Customers” table:

And a selection from the “Suppliers” table:

Example

The following SQL statement copies “Suppliers” into “Customers” (the columns that are not filled with data, will contain NULL):

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

The selection from the “Customers” table will now look like this:

Constraints

MatrixOne doesn’t support INSERT ... ON DUPLICATE KEY UPDATE statement yet. You can need to perform this operation with UPDATE.