INSERT Insert

This document describes how to insert data in MatrixOne using SQL statements.

INSERT INTO statement

The INSERT INTO statement has the following writing method:

1. Specify the column name and value to insert:

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

2. If you want to add values ​​to all columns of the table, you do not need to specify the column name in the SQL query. You must make sure that the order of values ​​is the same as the order of columns in the table. The INSERT INTO syntax is as follows:

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

3. Use the INSERT...VALUES... statement to insert multiple lines. A statement must contain multiple comma-separated lists of value, enclosed in parentheses and separated by commas. Examples are as follows:

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

Database Example

Here is an example selected from theCustomers 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)
  );

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 - 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland

INSERT INTO Example

The following SQL statement inserts a new record in theCustomers table:

Example

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

TheCustomers table is displayed as follows:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 - 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	Tom B. Erichsen	Skagen 21	Stavanger	4006	Norway

Insert data in the specified column only

MatrixOne also supports using SQL statements to insert data only in specific columns.

Example

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

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

TheCustomers table is displayed as follows:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
89	White Clover Markets	Karl Jablonski	305 - 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Wolski	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland
92	Cardinal	null	null	Stavanger	null	Norway

INSERT INTO...SELECT

Using INSERT INTO SELECT, you can quickly insert multiple rows into tables from the results of the SELECT statement, which can be selected from one or more tables. The INSERT INTO SELECT statement requires that the data types in the source and destination tables match.

INSERT INTO SELECT Syntax Explanation

Copy all columns from one table to another:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Copy only some columns from one table to another:

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

Northwind Database Example

Here is an example selected from a 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)
  );
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)
  );

TheCustomers table is displayed as follows:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

It seems like there's a typo in your example SQL statement and the surrounding text. The Insert into Customers statement is attempting to copy data from the suppliers table into a customers table, which is a common operation. However, the accompanying text is garbled.

Let's clarify the example and then show the expected customers table after the INSERT operation.

Original suppliers table

supplierid	Suppliername	Contactname	Address	City	Postalcode	Country
1	Exotic liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA
3	Grandma Kelly's Homestead	Regina Murphy	707 Oxford Rd.	ANN ARbor	48104	USA

Example SQL Statement (Corrected)

The SQL statement aims to copy the Suppliername, City, and Country columns from the suppliers table into the Customers table. Assuming the Customers table has columns named CustomerName, City, and Country (or similar, corresponding to the SELECT columns), the correct SQL would be:

INSERT INTO Customers (CustomerName, City, Country)
SELECT Suppliername, City, Country FROM suppliers;

Explanation of the SQL:

• INSERT INTO Customers (CustomerName, City, Country): This specifies that we are inserting data into the Customers table, and explicitly lists the columns we are populating.
• SELECT Suppliername, City, Country FROM suppliers;: This is a subquery that selects the Suppliername, City, and Country columns from the suppliers table. The results of this SELECT statement are then inserted into the specified columns of the Customers table.

Expected customers table after the INSERT operation

Assuming the customers table was empty before this operation and only has CustomerName, City, and Country columns (or at least these columns as part of its structure), after executing the INSERT statement, the customers table would look like this:

CustomerName	City	Country
Exotic liquid	Londona	UK
New Orleans Cajun Delights	New Orleans	USA
Grandma Kelly's Homestead	ANN ARbor	USA

Customerid	Customername	Contactname	Address	City	Postalcode	Country
1	Alfreds Futterkiste	Maria anders	OBERE STR. 57	Berlin	122209	Germany
2	Ana Trujillo Emaryadados y Helalas	Ana Trujillo	Avda. de la Constitution 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Exotic liquid	Null	Null	Londona	Null	UK
5	New Orleans Cajun Delights	Null	Null	New Orleans	Null	USA
6	Grandma Kelly's Homestead	Null	Null	ANN ARbor	Null	USA