INSERT Data Patterns

-- Insert single row
INSERT INTO users (name, email, status)
VALUES ('John Doe', 'john@example.com', 'active');

-- Insert multiple rows
INSERT INTO users (name, email, status)
VALUES 
  ('Alice', 'alice@example.com', 'active'),
  ('Bob', 'bob@example.com', 'pending'),
  ('Charlie', 'charlie@example.com', 'active');

-- Insert with auto-increment ID
INSERT INTO users (name, email)
VALUES ('Jane', 'jane@example.com');
-- ID is generated automatically

-- Insert and return ID (PostgreSQL)
INSERT INTO users (name, email)
VALUES ('Jane', 'jane@example.com')
RETURNING id;

-- Insert and return ID (MySQL)
INSERT INTO users (name, email)
VALUES ('Jane', 'jane@example.com');
SELECT LAST_INSERT_ID();

-- Insert from SELECT
INSERT INTO archived_users (name, email, archived_at)
SELECT name, email, NOW()
FROM users
WHERE status = 'deleted';

-- Insert with default values
INSERT INTO users (name, email)
VALUES ('Test', DEFAULT);

-- Insert or ignore duplicates (MySQL)
INSERT IGNORE INTO users (email, name)
VALUES ('existing@example.com', 'Name');

-- Insert or ignore (SQLite)
INSERT OR IGNORE INTO users (email, name)
VALUES ('existing@example.com', 'Name');

-- Upsert (ON CONFLICT - PostgreSQL)
INSERT INTO users (email, name, updated_at)
VALUES ('user@example.com', 'John', NOW())
ON CONFLICT (email) 
DO UPDATE SET 
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;

-- Upsert (MySQL)
INSERT INTO users (email, name, updated_at)
VALUES ('user@example.com', 'John', NOW())
ON DUPLICATE KEY UPDATE 
  name = VALUES(name),
  updated_at = VALUES(updated_at);