SQL | INSERT INTO Statement

The INSERT INTO statement is used to insert new records into a table in SQL.

There are two main ways you can use the INSERT INTO statement:

Inserting Full Rows:
You can specify both the column names and the values for the new row.
```
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
```
For example, if you have a table named students with columns id, first_name, and last_name, you can insert a new record like this:
```
INSERT INTO students (id, first_name, last_name)
VALUES (1, 'John', 'Doe');
```
Inserting Specific Columns:
If you only want to insert data into specific columns, you can do so by specifying just those columns. The other columns will get their default values (or NULL if no default is specified).
```
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);
```
Continuing with the students table example:
```
INSERT INTO students (first_name)
VALUES ('Jane');
```
If you omit the id and it is an auto-incrementing primary key, the database will automatically assign a value for you. If last_name has a default value or can accept NULL, then it will be filled accordingly.

Inserting Multiple Rows:

In many modern SQL databases, you can insert multiple rows in a single INSERT statement:

INSERT INTO table_name (column1, column2)
VALUES 
(value1a, value2a),
(value1b, value2b),
...;

For our students table:

INSERT INTO students (first_name, last_name)
VALUES 
('Jane', 'Smith'),
('Alice', 'Johnson');

Remember to always be cautious when using the INSERT statement to ensure you're adding the correct data to the correct table and columns. Always make backups and test on a sample database when possible.

How to Use INSERT INTO in SQL:
- Description: INSERT INTO is used to add new rows to a table.
- Example:
```
INSERT INTO employees (employee_id, employee_name, salary) VALUES (1, 'John Doe', 50000);
```

INSERT INTO Values Example in SQL:

INSERT INTO Values Example in SQL:
- Description: Values are specified for each column in the same order as columns in the table.
- Example:
```
INSERT INTO products (product_id, product_name, price) VALUES (101, 'Widget', 19.99);
```

SQL INSERT INTO Multiple Rows:

SQL INSERT INTO Multiple Rows:
- Description: Multiple rows can be inserted in a single statement.
- Example:
```
INSERT INTO customers (customer_id, customer_name) VALUES (1, 'John Doe'), (2, 'Jane Doe');
```

INSERT INTO SELECT Statement in SQL:

INSERT INTO SELECT Statement in SQL:

Description: Data from an existing table can be inserted into another table using INSERT INTO SELECT.

Example:

INSERT INTO archive_customers (customer_id, customer_name)
SELECT customer_id, customer_name FROM customers WHERE registration_date < '2022-01-01';

Using INSERT INTO with DEFAULT Values in SQL:

Using INSERT INTO with DEFAULT Values in SQL:
- Description: Columns with default values can be omitted in the INSERT INTO statement.
- Example:
```
INSERT INTO orders (order_id, product_id) VALUES (1, 101);
```

SQL INSERT INTO with WHERE Clause:

SQL INSERT INTO with WHERE Clause:

Description: INSERT INTO can be used with a WHERE clause to conditionally insert data.

Example:

INSERT INTO employees (employee_id, employee_name, salary)
SELECT user_id, user_name, salary FROM temp_users WHERE salary > 50000;

Handling NULL Values with INSERT INTO in SQL:

Handling NULL Values with INSERT INTO in SQL:
- Description: NULL values can be inserted explicitly or by omitting the column from the values list.
- Example:
```
INSERT INTO customers (customer_id, customer_name, email) VALUES (1, 'John Doe', NULL);
```

SQL INSERT INTO from Another Table:

SQL INSERT INTO from Another Table:
- Description: Data from one table can be inserted into another table directly.
- Example:
```
INSERT INTO new_customers SELECT * FROM old_customers;
```

Bulk Insert with INSERT INTO in SQL:

Bulk Insert with INSERT INTO in SQL:
- Description: Bulk inserts can be performed using INSERT INTO for better performance.
- Example:
```
INSERT INTO transactions (transaction_id, amount) VALUES
(1, 100), (2, 150), (3, 200), ... ;
```

SQL INSERT INTO with Variables:

SQL INSERT INTO with Variables:
- Description: Variables can be used in the INSERT INTO statement to insert dynamic values.
- Example:
```
DECLARE @product_name VARCHAR(255) = 'New Widget';
INSERT INTO products (product_name) VALUES (@product_name);
```

Duplicate Key Handling in SQL INSERT INTO:

Duplicate Key Handling in SQL INSERT INTO:
- Description: Duplicate key violations can be managed using techniques like INSERT IGNORE or ON DUPLICATE KEY UPDATE.
- Example (INSERT IGNORE):
```
INSERT IGNORE INTO products (product_id, product_name) VALUES (101, 'Widget');
```

Examples of SQL INSERT INTO with Subquery:

Examples of SQL INSERT INTO with Subquery:

Description: A subquery can be used to provide values for the INSERT INTO statement.

Example:

INSERT INTO audit_log (user_id, action, timestamp)
SELECT user_id, 'LOGIN', CURRENT_TIMESTAMP FROM users WHERE username = 'john_doe';

Constraints and INSERT INTO in SQL:

Constraints and INSERT INTO in SQL:
- Description: INSERT INTO should comply with table constraints, such as NOT NULL, UNIQUE, and foreign key constraints.
- Example:
```
INSERT INTO orders (order_id, product_id, quantity) VALUES (1, 101, 3);
```

Common Errors with SQL INSERT INTO:

Common Errors with SQL INSERT INTO:
- Description: Errors may occur due to data type mismatches, constraint violations, or incomplete values.
- Example:
```
INSERT INTO employees (employee_id, employee_name) VALUES (1, 'John Doe', 50000);
```