1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-- Single statement inserting multiple rows
INSERT INTO products (product_id, product_name, unit_price, category_id)
VALUES 
    (1001, 'Wireless Mouse', 29.99, 1),
    (1002, 'Mechanical Keyboard', 149.99, 1),
    (1003, 'USB-C Hub', 49.99, 1),
    (1004, 'Monitor Stand', 79.99, 2),
    (1005, 'Desk Lamp', 34.99, 2),
    -- ... potentially hundreds more rows
    (1100, 'Cable Organizer', 12.99, 2);
 
-- One parse, one execution plan, one commit
-- 100 rows inserted with overhead of 1 statement
 
-- Practical batch size: 100-1000 rows per INSERT
-- Limited by:
--   1. Maximum statement size (varies by database, often 1-64 MB)
--   2. Maximum parameters (e.g., SQL Server: 2100)
--   3. Memory for parsing large statements

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- Copy data between tables (no application round-trip)
INSERT INTO orders_archive (order_id, customer_id, order_date, total_amount, archived_at)
SELECT order_id, customer_id, order_date, total_amount, CURRENT_TIMESTAMP
FROM orders
WHERE order_date < DATE '2023-01-01';
-- Moves millions of rows in seconds
 
-- Create summary table from detailed data
INSERT INTO monthly_sales (year_month, category, total_revenue, order_count)
SELECT 
    TO_CHAR(order_date, 'YYYY-MM'),
    p.category,
    SUM(oi.quantity * oi.unit_price),
    COUNT(DISTINCT o.order_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY TO_CHAR(order_date, 'YYYY-MM'), p.category;
-- Aggregates and inserts in one operation
 
-- Clone table structure and data
CREATE TABLE products_backup AS
SELECT * FROM products;
-- Or: INSERT INTO products_backup SELECT * FROM products;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# Application-level batching (Python example)
import psycopg2
 
def bulk_insert_products(products, batch_size=1000):
    """Insert products in batches for optimal performance."""
    conn = psycopg2.connect(database="mydb")
    cursor = conn.cursor()
    
    # Process in batches
    for i in range(0, len(products), batch_size):
        batch = products[i:i + batch_size]
        
        # Build multi-row INSERT
        values_list = []
        params = []
        for idx, product in enumerate(batch):
            base = idx * 4  # 4 params per row
            values_list.append(f"(%s, %s, %s, %s)")
            params.extend([
                product['id'],
                product['name'],
                product['price'],
                product['category_id']
            ])
        
        sql = f"""
            INSERT INTO products (product_id, product_name, unit_price, category_id)
            VALUES {', '.join(values_list)}
        """
        
        cursor.execute(sql, params)
    
    conn.commit()
    cursor.close()
    conn.close()
 
# Usage: Insert 100,000 products in ~100 batches
products = [{"id": i, "name": f"Product {i}", "price": 9.99, "category_id": 1} 
            for i in range(100000)]
bulk_insert_products(products)  # Completes in seconds, not minutes

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- PostgreSQL COPY: Fastest way to load/unload data
 
-- COPY from file (server-side file path)
COPY products (product_id, product_name, unit_price, category_id)
FROM '/var/lib/postgresql/data/products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');
 
-- COPY from STDIN (stream data from application)
COPY products (product_id, product_name, unit_price, category_id)
FROM STDIN WITH (FORMAT csv);
-- Then send CSV data followed by \. to terminate
 
-- COPY to file (export)
COPY (SELECT * FROM products WHERE category_id = 1)
TO '/tmp/electronics.csv'
WITH (FORMAT csv, HEADER true);
 
-- Client-side \copy (psql command, uses client file paths)
\copy products FROM 'C:/data/products.csv' WITH (FORMAT csv, HEADER true)
 
-- COPY with error handling (PostgreSQL 16+)
COPY products FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR 'ignore');
-- Skips malformed rows instead of failing entire operation

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- SQL Server: BULK INSERT command
BULK INSERT products
FROM 'C:\Data\products.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '
',
    FIRSTROW = 2,              -- Skip header row
    TABLOCK,                   -- Table-level lock for speed
    BATCHSIZE = 100000,        -- Commit every 100K rows
    MAXERRORS = 100            -- Allow up to 100 errors
);
 
-- Format file for complex mappings
BULK INSERT products
FROM 'C:\Data\products.dat'
WITH (
    FORMATFILE = 'C:\Data\products.fmt',
    ERRORFILE = 'C:\Data\errors.log'
);
 
-- BCP utility (command line) - bidirectional
-- Export data
-- bcp MyDatabase.dbo.products out C:\Data\products.dat -c -T
 
-- Import data  
-- bcp MyDatabase.dbo.products in C:\Data\products.dat -c -T -b 10000
 
-- bcp with format file for production loads
-- bcp MyDatabase.dbo.products in data.dat -f products.fmt -T -b 50000 -a 65535

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

-- MySQL: LOAD DATA INFILE (fastest bulk insert)
LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 LINES  -- Skip header
(product_id, product_name, unit_price, category_id);
 
-- LOAD DATA LOCAL (client-side file)
LOAD DATA LOCAL INFILE 'C:/Data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
IGNORE 1 LINES;
 
-- With column transformations
LOAD DATA INFILE '/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'
IGNORE 1 LINES
(product_id, product_name, @price, category_id)
SET unit_price = @price * 1.1,  -- Apply 10% markup
    created_at = NOW();
 
-- Handling duplicates
LOAD DATA INFILE '/data/products.csv'
REPLACE  -- Update existing rows
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- Batched DELETE: Process in chunks with commits between
 
-- SQL Server: Batched delete with row count
DECLARE @BatchSize INT = 10000;
DECLARE @RowsAffected INT = 1;
 
WHILE @RowsAffected > 0
BEGIN
    DELETE TOP (@BatchSize) FROM order_items
    WHERE order_id IN (
        SELECT order_id FROM orders 
        WHERE order_date < '2020-01-01'
    );
    
    SET @RowsAffected = @@ROWCOUNT;
    
    -- Optional: Brief pause to reduce lock contention
    WAITFOR DELAY '00:00:01';
END
 
-- PostgreSQL: Using ctid for efficient chunking
DO $$
DECLARE
    batch_size INTEGER := 10000;
    deleted_count INTEGER;
BEGIN
    LOOP
        DELETE FROM order_items
        WHERE ctid IN (
            SELECT ctid FROM order_items
            WHERE order_id IN (
                SELECT order_id FROM orders WHERE order_date < '2020-01-01'
            )
            LIMIT batch_size
        );
        
        GET DIAGNOSTICS deleted_count = ROW_COUNT;
        
        IF deleted_count = 0 THEN
            EXIT;
        END IF;
        
        COMMIT;  -- Release locks between batches
    END LOOP;
END $$;
 
-- MySQL: LIMIT with DELETE
DELIMITER //
CREATE PROCEDURE BatchDeleteOldItems()
BEGIN
    DECLARE done INT DEFAULT 0;
    
    REPEAT
        DELETE FROM order_items
        WHERE order_id IN (
            SELECT order_id FROM orders WHERE order_date < '2020-01-01'
        )
        LIMIT 10000;
        
        SET done = ROW_COUNT() = 0;
        
        -- Commit after each batch
        COMMIT;
    UNTIL done END REPEAT;
END //
DELIMITER ;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

-- Batched UPDATE: Same principle, process in chunks
 
-- SQL Server: Batched update with progress tracking
DECLARE @BatchSize INT = 50000;
DECLARE @TotalUpdated INT = 0;
DECLARE @RowsAffected INT = 1;
 
WHILE @RowsAffected > 0
BEGIN
    UPDATE TOP (@BatchSize) products
    SET unit_price = unit_price * 1.10,
        last_price_update = GETDATE()
    WHERE category_id = 5
      AND last_price_update < DATEADD(YEAR, -1, GETDATE());
    
    SET @RowsAffected = @@ROWCOUNT;
    SET @TotalUpdated = @TotalUpdated + @RowsAffected;
    
    -- Log progress
    PRINT 'Updated ' + CAST(@TotalUpdated AS VARCHAR) + ' rows so far...';
    
    -- Checkpoint: reduce log pressure
    CHECKPOINT;
END
 
PRINT 'Total updated: ' + CAST(@TotalUpdated AS VARCHAR);
 
-- PostgreSQL: Batched update with RETURNING for tracking
DO $$
DECLARE
    batch_size INTEGER := 50000;
    total_updated INTEGER := 0;
    updated_count INTEGER;
BEGIN
    LOOP
        WITH updated AS (
            UPDATE products
            SET unit_price = unit_price * 1.10,
                last_price_update = NOW()
            WHERE product_id IN (
                SELECT product_id FROM products
                WHERE category_id = 5
                  AND last_price_update < NOW() - INTERVAL '1 year'
                LIMIT batch_size
                FOR UPDATE SKIP LOCKED  -- Avoid lock contention
            )
            RETURNING 1
        )
        SELECT COUNT(*) INTO updated_count FROM updated;
        
        total_updated := total_updated + updated_count;
        RAISE NOTICE 'Updated % rows so far', total_updated;
        
        IF updated_count < batch_size THEN
            EXIT;
        END IF;
        
        COMMIT;
    END LOOP;
    
    RAISE NOTICE 'Total updated: %', total_updated;
END $$;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- SQL Server Minimal Logging Requirements:
-- 1. Database in SIMPLE or BULK_LOGGED recovery model
-- 2. Table has no clustered index (heap) OR using TABLOCK hint
-- 3. Specific operations (BULK INSERT, SELECT INTO, INSERT...SELECT)
 
-- Check current recovery model
SELECT name, recovery_model_desc 
FROM sys.databases 
WHERE name = DB_NAME();
 
-- Temporarily switch to BULK_LOGGED for large import
ALTER DATABASE MyDatabase SET RECOVERY BULK_LOGGED;
 
-- Bulk insert with minimal logging
INSERT INTO new_products WITH (TABLOCK)
SELECT * FROM staging_products;
-- TABLOCK hint enables minimal logging
 
-- Or use INSERT INTO with heap (no clustered index)
BULK INSERT products_heap
FROM 'C:\data\products.csv'
WITH (TABLOCK, BATCHSIZE = 100000);
 
-- Restore full recovery after bulk operation
ALTER DATABASE MyDatabase SET RECOVERY FULL;
 
-- Take log backup to establish new recovery chain
BACKUP LOG MyDatabase TO DISK = 'C:\backups\MyDatabase_log.bak';
 
-- Verify minimal logging occurred
SELECT 
    operation,
    COUNT(*) as operation_count
FROM fn_dblog(NULL, NULL)
WHERE operation IN ('LOP_INSERT_ROWS', 'LOP_FORMAT_PAGE')
GROUP BY operation;
-- LOP_FORMAT_PAGE indicates minimal logging (page allocations only)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- PostgreSQL: UNLOGGED tables skip WAL for inserts
-- WARNING: Data is NOT crash-safe! For staging/temp data only.
 
-- Create unlogged staging table
CREATE UNLOGGED TABLE staging_products (
    product_id      INTEGER,
    product_name    VARCHAR(200),
    unit_price      NUMERIC(10, 2),
    category_id     INTEGER
);
 
-- COPY into unlogged table (very fast)
COPY staging_products FROM '/data/products.csv' WITH (FORMAT csv);
-- No WAL written = much faster
 
-- Transform and move to logged table
INSERT INTO products
SELECT * FROM staging_products
WHERE unit_price > 0;
 
-- Clean up staging
DROP TABLE staging_products;
 
-- Alternative: Make table temporarily unlogged
-- (PostgreSQL doesn't support this, but pattern for MySQL)
 
-- Check if table is unlogged
SELECT relname, relpersistence
FROM pg_class
WHERE relname = 'staging_products';
-- 'u' = unlogged, 'p' = permanent (logged), 't' = temporary

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- MySQL: Performance optimizations for bulk inserts
 
-- 1. Disable key checking temporarily
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
 
-- 2. Disable autocommit
SET autocommit = 0;
 
-- Perform bulk inserts
LOAD DATA INFILE '/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
';
 
-- 3. Re-enable and commit
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
COMMIT;
SET autocommit = 1;
 
-- 4. For InnoDB: Adjust buffer pool and log settings
-- (in my.cnf, before bulk operation)
-- innodb_buffer_pool_size = 4G
-- innodb_log_file_size = 256M
-- innodb_flush_log_at_trx_commit = 2  -- Less durable, faster
 
-- 5. Disable binary logging for session (careful!)
SET sql_log_bin = 0;
-- WARNING: Breaks replication! Only for initial loads
LOAD DATA INFILE '/data/massive_data.csv' INTO TABLE target;
SET sql_log_bin = 1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- Pattern: Drop indexes → Load data → Recreate indexes
 
-- 1. Script out existing indexes
-- (Use database tools or catalog queries to capture index definitions)
 
SELECT 
    'CREATE INDEX ' || indexname || ' ON ' || tablename || 
    ' (' || indexdef || ');' as create_script
FROM pg_indexes
WHERE tablename = 'products' AND indexname != 'products_pkey';
 
-- 2. Drop non-primary indexes
DROP INDEX idx_products_category;
DROP INDEX idx_products_name;
DROP INDEX idx_products_supplier;
 
-- 3. Perform bulk load (much faster without indexes)
COPY products FROM '/data/massive_products.csv' WITH (FORMAT csv);
 
-- 4. Recreate indexes (can parallelize)
CREATE INDEX CONCURRENTLY idx_products_category ON products(category_id);
CREATE INDEX CONCURRENTLY idx_products_name ON products(product_name);
CREATE INDEX CONCURRENTLY idx_products_supplier ON products(supplier_id);
 
-- CONCURRENTLY: Allows reads/writes during index creation (PostgreSQL)
-- Without CONCURRENTLY: Faster creation but table is locked

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Temporarily disable constraints for bulk load
 
-- SQL Server: Disable and re-enable constraints
ALTER TABLE order_items NOCHECK CONSTRAINT FK_OrderItems_Products;
ALTER TABLE order_items NOCHECK CONSTRAINT FK_OrderItems_Orders;
 
-- Bulk load
BULK INSERT order_items FROM 'C:\data\order_items.csv' WITH (TABLOCK);
 
-- Re-enable and validate
ALTER TABLE order_items WITH CHECK CHECK CONSTRAINT FK_OrderItems_Products;
ALTER TABLE order_items WITH CHECK CHECK CONSTRAINT FK_OrderItems_Orders;
-- WITH CHECK: Validates existing data against constraint
 
-- PostgreSQL: Defer constraint checking
-- Constraints must be defined as DEFERRABLE
ALTER TABLE order_items 
    ADD CONSTRAINT fk_order_items_orders 
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
    DEFERRABLE INITIALLY DEFERRED;
 
-- Constraints are checked at COMMIT, not per-statement
BEGIN;
    -- Insert child rows before parent rows (normally invalid)
    INSERT INTO order_items VALUES (1, 9999, 101, 5, 9.99);
    -- Parent order_id 9999 doesn't exist yet...
    
    INSERT INTO orders VALUES (9999, 42, '2024-01-15');
    -- Now parent exists, constraint satisfied at COMMIT
COMMIT;
 
-- MySQL: Temporarily disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;
LOAD DATA INFILE '/data/order_items.csv' INTO TABLE order_items;
SET FOREIGN_KEY_CHECKS = 1;
 
-- Validate data integrity after re-enabling
SELECT oi.* FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE p.product_id IS NULL;
-- Should return 0 rows if data is valid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- PostgreSQL: Parallel COPY (since 14, for COPY TO)
-- COPY TO parallelizes across workers
COPY (SELECT * FROM products) TO PROGRAM 'split -l 1000000' WITH (FORMAT csv);
 
-- For parallel INSERT, use pg_partman or custom partitioning
-- Each partition can be loaded independently in parallel
 
-- SQL Server: Parallel INSERT with hints
INSERT INTO products WITH (TABLOCK) 
SELECT * FROM staging_products
OPTION (MAXDOP 8);  -- Use up to 8 parallel threads
 
-- Oracle: Parallel DML
ALTER SESSION ENABLE PARALLEL DML;
 
INSERT /*+ PARALLEL(products, 8) */ INTO products
SELECT /*+ PARALLEL(staging, 8) */ * FROM staging_products;
 
-- Parallel direct path insert
INSERT /*+ APPEND PARALLEL(products, 8) */ INTO products
SELECT * FROM staging_products;
 
ALTER SESSION DISABLE PARALLEL DML;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- Load directly into specific partitions
-- Avoids partition routing overhead
 
-- PostgreSQL: Partitioned table
CREATE TABLE orders (
    order_id        BIGINT,
    order_date      DATE,
    customer_id     INTEGER,
    total_amount    NUMERIC(12, 2)
) PARTITION BY RANGE (order_date);
 
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
 
CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
 
-- Load directly into partition (bypasses routing)
COPY orders_2024_q1 FROM '/data/orders_2024_q1.csv' WITH (FORMAT csv);
COPY orders_2024_q2 FROM '/data/orders_2024_q2.csv' WITH (FORMAT csv);
 
-- Parallel loading: Load each partition concurrently
-- Run in separate connections/sessions:
-- Session 1: COPY orders_2024_q1 FROM '/data/q1.csv'...
-- Session 2: COPY orders_2024_q2 FROM '/data/q2.csv'...
 
-- SQL Server: Partition switching for instant loads
-- Load into staging table with same structure
BULK INSERT staging_orders_q1 FROM 'C:\data\q1.csv' WITH (TABLOCK);
 
-- Create matching indexes and constraints on staging
-- Then switch partition (instant, metadata-only)
ALTER TABLE staging_orders_q1 SWITCH TO orders PARTITION 1;
 
-- MySQL: LOAD DATA with partitioning
LOAD DATA INFILE '/data/orders_2024_q1.csv'
INTO TABLE orders
PARTITION (p_2024_q1)  -- Specify target partition
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

-- PostgreSQL: Monitor running COPY
SELECT 
    pid,
    query_start,
    NOW() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE query ILIKE '%COPY%' AND state = 'active';
 
-- Check table size growth during load
SELECT 
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS size,
    n_live_tup AS row_estimate
FROM pg_stat_user_tables
WHERE relname = 'products';
 
-- SQL Server: Monitor bulk operation progress
SELECT 
    r.session_id,
    r.command,
    r.percent_complete,
    r.estimated_completion_time / 1000 / 60 AS est_minutes_remaining,
    t.text AS sql_statement
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.command IN ('BULK INSERT', 'INSERT');
 
-- Check transaction log usage
SELECT 
    name,
    log_reuse_wait_desc,
    ROUND(CAST(size AS FLOAT) * 8 / 1024, 2) AS log_size_mb,
    ROUND(CAST(size AS FLOAT) * 8 / 1024 * (1 - CAST(max_size AS FLOAT) / size), 2) AS log_used_mb
FROM sys.master_files
WHERE database_id = DB_ID() AND type_desc = 'LOG';
 
-- MySQL: Monitor LOAD DATA
SHOW PROCESSLIST;
 
-- Check InnoDB buffer pool hit rate
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
 
-- Monitor table row count during load
SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_NAME = 'products';