Inserting Data into Relational Databases

This tutorial covers the fundamental methods for inserting new data into relational database tables using SQL. Understanding how to populate your tables is a crucial step in database management and application development.

1. The Basic INSERT Statement

The simplest way to add data is by using the `INSERT INTO` statement. You specify the table name and then provide the values for each column in the order they appear in the table schema.

INSERT INTO Customers (CustomerID, CompanyName, ContactName, City, Country)
VALUES (1, 'Microsoft', 'Bill Gates', 'Redmond', 'USA');

2. Inserting Values for Specific Columns

Often, you may not want to insert values for every column, perhaps because some columns have default values or are optional. In such cases, you can explicitly list the columns you are providing values for.

INSERT INTO Products (ProductID, ProductName, UnitPrice)
VALUES (101, 'Chai', 18.00);

3. Inserting Multiple Rows

To insert multiple records efficiently, you can provide multiple sets of values separated by commas after the `VALUES` keyword.

INSERT INTO Suppliers (SupplierID, CompanyName, City)
VALUES
  (30, 'Exotic Liquids', 'London'),
  (31, 'New Orleans Cajun Delights', 'New Orleans'),
  (32, 'Grandma Kelly''s Homestead', 'Ann Arbor');

4. Inserting Data from Another Table

You can also insert data into a table by selecting it from another table using an `INSERT INTO ... SELECT` statement. This is useful for copying or migrating data.

-- Assume Employees table has columns: EmployeeID, FirstName, LastName, HireDate
-- Assume NewHires table has columns: EmployeeID, FirstName, LastName, HireDate

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
SELECT EmployeeID, FirstName, LastName, HireDate
FROM NewHires
WHERE HireDate >= '2023-01-01';

Important Considerations:

Ensure the data types of the values match the data types of the columns.
Respect constraints such as NOT NULL, UNIQUE, and FOREIGN KEY.
If a column has an identity or auto-increment property, you generally do not need to provide a value for it.
Be mindful of SQL injection vulnerabilities if constructing queries with user-provided input. Use parameterized queries where appropriate.

5. Inserting into Temporary Tables

Temporary tables are useful for holding intermediate results. You can insert data into them using the same `INSERT INTO` syntax.

-- Create a temporary table (syntax may vary slightly between SQL dialects)
CREATE TEMPORARY TABLE TempOrders AS
SELECT OrderID, OrderDate
FROM Orders
WHERE CustomerID = 5;

-- Insert data from a query into an existing temporary table
INSERT INTO TempOrders (OrderID, OrderDate)
SELECT OrderID, OrderDate
FROM Orders
WHERE CustomerID = 10;