SQL INSERT INTO

SQL
POSTGRES
INTERACTIVE

Complete SQL INSERT INTO Technical Guide

1. Fundamental Syntax

Explicit Column Listing:

INSERT INTO table_name (col1, col2, col3)
VALUES (val1, val2, val3);

Implicit Column Listing:

INSERT INTO table_name
VALUES (val1, val2, val3);
2. Advanced Insert Methods
Multi-Row Insert
INSERT INTO products (name, price)
VALUES 
  ('Product A', 9.99),
  ('Product B', 19.99),
  ('Product C', 29.99);

More efficient than single inserts (reduced roundtrips)

Insert from Select
INSERT INTO archived_orders
SELECT * FROM orders
WHERE order_date < '2020-01-01';
Insert with Defaults
INSERT INTO users (username)
VALUES ('johndoe');
-- Other columns get DEFAULT values
3. Database-Specific Features
MySQL
INSERT IGNORE INTO table ...
-- Skips rows with errors
INSERT INTO table ... ON DUPLICATE KEY UPDATE ...
-- Updates if duplicate exists
PostgreSQL
INSERT INTO table ... RETURNING id
-- Returns generated values
INSERT INTO table ... ON CONFLICT DO NOTHING
-- Handles conflicts gracefully
4. Performance Considerations
Batch Insert Optimization
  1. Use multi-row VALUES syntax
  2. Consider prepared statements
  3. Batch size typically 100-1000 rows
Index Impact
  1. Each index adds insert overhead
  2. Consider dropping/recreating indexes for bulk loads
  3. Foreign keys add validation cost
Bulk Load Techniques
-- MySQL
LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ',';

-- PostgreSQL
COPY my_table FROM '/path/to/file.csv' DELIMITER ',';
5. Error Handling
Common Errors
  1. Constraint violations (PK, FK, UNIQUE)
  2. Data type mismatches
  3. NOT NULL constraint violations
Handling Strategies
  1. Use database-specific modifiers (IGNORE, ON CONFLICT)
  2. Validate data before insert
  3. Use transactions for atomicity
6. Advanced Patterns
Insert with Joins
INSERT INTO user_orders (user_id, order_id)
SELECT u.id, o.id
FROM users u
JOIN orders o ON u.email = o.customer_email;
Conditional Insert
INSERT INTO inventory
SELECT product_id, quantity
FROM shipment
WHERE quantity > 0;
Insert with CTEs
WITH new_users AS (
  SELECT * FROM temp_users WHERE valid = true
)
INSERT INTO main_users
SELECT * FROM new_users;
RETURNING Clause
INSERT INTO users (name, email) 
VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;

Supported in PostgreSQL, Oracle, and SQLite. Useful for getting auto-generated values.

Bulk Insert Optimization
  1. Transaction Batching: Wrap multiple inserts in a single transaction
  2. Batch Size: Optimal sizes vary (typically 500-5000 rows per batch)
  3. Disable Constraints: Temporarily disable triggers/checks during bulk loads 
-- SQL Server example
BEGIN TRANSACTION;
INSERT INTO table VALUES (...);
INSERT INTO table VALUES (...);
COMMIT;
Conflict Handling
MySQL
INSERT INTO table (id, name)
VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name='Alice';
PostgreSQL
INSERT INTO table (id, name)
VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name=EXCLUDED.name;
Generated/Virtual Columns 
-- MySQL example
CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2),
  tax DECIMAL(10,2) GENERATED ALWAYS AS (price * 0.1)
);

-- Insert ignores generated columns
INSERT INTO products (id, price) VALUES (1, 100.00);
Security Best Practices
  1. Use Prepared Statements to prevent SQL injection
  2. Validate Input before insertion
  3. Limit Permissions: Application users shouldn't have full write access 
-- Parameterized query example (Python)
cursor.execute(
  "INSERT INTO users (name) VALUES (%s)",
  (user_input,)
)
Partitioned Table Inserts 
-- PostgreSQL example
INSERT INTO sales (sale_date, amount)
VALUES ('2023-01-15', 99.99)
-- Automatically routes to correct partition

Requires proper partition key in INSERT values

Materialized View Impact 
-- After bulk inserts
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Large inserts may require manual refresh of dependent views