04. Inserting Data Into Tables

Level: Beginner

ℹ️ What You'll Learn

• Basic INSERT syntax -- add single row
• Insert multiple rows at once
• INSERT with specific columns
• Handle IDENTITY columns (auto-increment)
• Insert using DEFAULT values
• Copy data from one table to another
• Common INSERT errors & fixes

Now that tables exist, they're empty. Data must be added. This article teaches INSERT -- the C in CRUD (Create, Read, Update, Delete).

Why INSERT Matters for Backend Developers

Every API that creates data uses INSERT:

Frontend (React/Angular)
    v
"Add new student: Ravi Kumar"
    v
C# Backend API (POST request)
    v
INSERT INTO Student VALUES (...)
    v
SQL Server stores in Student table

Without INSERT, your API cannot save data.

Basic INSERT Syntax

INSERT INTO TableName (Column1, Column2, Column3)
VALUES (Value1, Value2, Value3);

Parts:

• INSERT INTO -- Command to add data
• TableName -- Which table to insert into
• (Column1, Column2) -- Which columns to fill
• VALUES (...) -- What values to insert

Example 1: Insert Single Student

From article 03, Student table has: StudentId, Name, RollNumber, ClassName, Section, DateOfBirth, ParentName, ParentPhone, Address, Status.

INSERT INTO Student (Name, RollNumber, ClassName, Section, Status)
VALUES ('Ravi Kumar', 101, '10-A', 'A', 'Active');

What happens:

• StudentId = auto-assigned (IDENTITY)
• Name = 'Ravi Kumar'
• RollNumber = 101
• ClassName = '10-A'
• Section = 'A'
• Status = 'Active' (matches DEFAULT)
• DateOfBirth, ParentName, ParentPhone, Address = NULL (optional columns)

Run this in SSMS. Success message: (1 row affected).

Check data:

SELECT * FROM Student;

Result:

StudentId | Name        | RollNumber | ClassName | Section | Status
1         | Ravi Kumar  | 101        | 10-A      | A       | Active

Example 2: Insert Multiple Rows

Add 3 students at once:

INSERT INTO Student (Name, RollNumber, ClassName, Section, Status)
VALUES 
    ('Priya Sharma', 102, '10-A', 'A', 'Active'),
    ('Arjun Reddy', 103, '10-B', 'B', 'Active'),
    ('Sneha Patel', 104, '10-B', 'B', 'Active');

Success: (3 rows affected).

Why useful: Insert many students in one query instead of 3 separate queries. Faster, less network traffic.

Example 3: Why You Should List Columns

In SQL Server, StudentId is an IDENTITY column, so you should not provide it manually.

Wrong:

INSERT INTO Student
VALUES (NULL, 'Kiran Rao', 105, '10-C', 'C', '2010-05-20', 'Mr. Rao', '9876543210', '123 Main St', 'Active');

This fails because SQL Server does not auto-fill an identity column when you explicitly pass NULL.

Right:

INSERT INTO Student (Name, RollNumber, ClassName, Section, DateOfBirth, ParentName, ParentPhone, Address, Status)
VALUES ('Kiran Rao', 105, '10-C', 'C', '2010-05-20', 'Mr. Rao', '9876543210', '123 Main St', 'Active');

SQL Server assigns StudentId automatically. Listing columns is explicit, clear, and safe if the table changes later.

Example 4: Insert With DEFAULT Values

Recall from article 03: Status has DEFAULT 'Active'.

INSERT INTO Student (Name, RollNumber, ClassName, Section)
VALUES ('Vikram Singh', 106, '10-C', 'C');

Don't specify Status. SQL Server fills it as 'Active' automatically.

Check:

SELECT Name, Status FROM Student WHERE Name = 'Vikram Singh';

Result: Status = 'Active' (from DEFAULT).

Example 5: Insert Into Specific Columns (Skip Optional)

Insert only required fields, leave optional blank:

INSERT INTO Student (Name, RollNumber, ClassName, Section)
VALUES ('Meera Gupta', 107, '10-D', 'D');

DateOfBirth, ParentName, ParentPhone, Address = NULL (not provided).

Check:

SELECT Name, DateOfBirth, ParentName FROM Student WHERE Name = 'Meera Gupta';

Result:

Name         | DateOfBirth | ParentName
Meera Gupta  | NULL        | NULL

Example 6: Insert From Another Table (INSERT...SELECT)

Say you have a temporary table with new students. Copy into Student table:

-- Assume TempStudents table exists with same structure
INSERT INTO Student (Name, RollNumber, ClassName, Section, Status)
SELECT Name, RollNumber, ClassName, Section, Status FROM TempStudents;

SQL Server inserts all rows from TempStudents into Student.

Real use case: Bulk import students from CSV -> TempStudents -> then INSERT...SELECT into Student.

Understanding IDENTITY Columns

From article 03: StudentId INT PRIMARY KEY IDENTITY(1,1).

IDENTITY means:

• SQL Server auto-assigns
• You never provide StudentId
• First row = 1, second = 2, etc.

-- WRONG -- don't provide StudentId
INSERT INTO Student (StudentId, Name, RollNumber, ClassName)
VALUES (10, 'Test', 200, '10-E');
-- Error: Cannot insert explicit value for IDENTITY column

-- RIGHT -- omit StudentId
INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Test', 200, '10-E');
-- StudentId auto-assigned (next available number)

Inserting Teacher Records

Teacher table from article 03:

CREATE TABLE Teacher
(
    TeacherId INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    EmployeeCode VARCHAR(20) NOT NULL UNIQUE,
    Qualification VARCHAR(50),
    ExperienceYears INT,
    Salary DECIMAL(10,2),
    JoiningDate DATE,
    IsActive BIT DEFAULT 1
);

Insert 3 teachers:

INSERT INTO Teacher (Name, EmployeeCode, Qualification, ExperienceYears, Salary, JoiningDate, IsActive)
VALUES 
    ('Dr. Mehta', 'EMP001', 'M.Sc', 8, 50000.00, '2016-01-15', 1),
    ('Mrs. Rao', 'EMP002', 'B.Ed', 12, 55000.00, '2012-06-10', 1),
    ('Mr. Sharma', 'EMP003', 'M.Tech', 5, 45000.00, '2019-09-01', 1);

Check:

SELECT * FROM Teacher;

Result:

TeacherId | Name        | EmployeeCode | Qualification | ExperienceYears | Salary  | JoiningDate | IsActive
1         | Dr. Mehta   | EMP001       | M.Sc          | 8               | 50000.0 | 2016-01-15  | 1
2         | Mrs. Rao    | EMP002       | B.Ed          | 12              | 55000.0 | 2012-06-10  | 1
3         | Mr. Sharma  | EMP003       | M.Tech        | 5               | 45000.0 | 2019-09-01  | 1

Common INSERT Errors

Error 1: NOT NULL column empty

INSERT INTO Student (RollNumber, ClassName)
VALUES (101, '10-A');
-- Error: Column 'Name' cannot be null

Fix: Provide Name.

Error 2: Data type mismatch

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Ravi', 'ABC', '10-A');
-- Error: Conversion failed from string 'ABC' to int

RollNumber is INT, but provided 'ABC' (text). Fix: Use number.

Error 3: UNIQUE constraint violation

INSERT INTO Teacher (Name, EmployeeCode, Qualification)
VALUES ('New Teacher', 'EMP001', 'B.Ed');
-- Error: Violation of UNIQUE KEY constraint 'PK_Teacher_EmployeeCode'
-- EMP001 already exists

Fix: Use unique EmployeeCode.

Error 4: Column doesn't exist

INSERT INTO Student (Name, RollNum)
VALUES ('Ravi', 101);
-- Error: Invalid column name 'RollNum'

Column is RollNumber, not RollNum. Fix: Correct column name.

Best Practices for INSERT

1. Always specify columns -- Don't rely on table order
2. Check for required fields -- NOT NULL columns must have values
3. Validate UNIQUE constraints -- Don't duplicate EmployeeCode
4. Use transactions for bulk -- Ensure all-or-nothing
5. Check data types -- INT for numbers, VARCHAR for text
6. Use meaningful values -- Don't insert test data into production
7. Consider IDENTITY -- Never manually provide auto-increment IDs

---

🎯 Q1: What's the difference between INSERT with column names vs without?

With column names:

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Ravi', 101, '10-A');

Explicit, clear, safe. Works even if table schema changes.

Without column names:

INSERT INTO Student
VALUES (NULL, 'Ravi', 101, '10-A', 'A', NULL, NULL, NULL, NULL, 'Active');

Fails for an IDENTITY column and relies on exact column order. Fragile. If columns change, breaks.

Best practice: Always use column names.

🎯 Q2: Can I insert a row without providing all columns?

Yes, if:

• Column has DEFAULT value
• Column allows NULL
• Column is IDENTITY (auto-assigned)

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Ravi', 101, '10-A');
-- DateOfBirth = NULL (optional)
-- Section = NULL (optional)
-- Status = 'Active' (DEFAULT)
-- StudentId = auto-assigned (IDENTITY)

No, if column:

• Is NOT NULL
• Has no DEFAULT
• Is not IDENTITY

INSERT INTO Student (RollNumber, ClassName)
VALUES (101, '10-A');
-- Error: Name is required (NOT NULL)

🎯 Q3: What does IDENTITY(1,1) mean in INSERT?

IDENTITY(1,1) means:

• Start at 1
• Increment by 1
• SQL Server manages it

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Ravi', 101, '10-A');
-- StudentId = 1 (auto)

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Priya', 102, '10-A');
-- StudentId = 2 (auto)

INSERT INTO Student (Name, RollNumber, ClassName)
VALUES ('Arjun', 103, '10-B');
-- StudentId = 3 (auto)

Never manually provide StudentId. SQL Server assigns automatically.

🎯 Q4: How do I insert data from one table into another?

Use INSERT...SELECT:

INSERT INTO Student (Name, RollNumber, ClassName, Section, Status)
SELECT Name, RollNumber, ClassName, Section, Status FROM TempStudents;

Copies all rows from TempStudents -> Student.

Real use case: Import students from CSV:

1. CSV -> Load into TempStudents table
2. Validate data in TempStudents
3. INSERT...SELECT into Student
4. Delete TempStudents

With filter:

INSERT INTO Student (Name, RollNumber, ClassName, Section, Status)
SELECT Name, RollNumber, ClassName, Section, Status 
FROM TempStudents 
WHERE Status = 'Active';
-- Only insert active students

🎯 Q5: What's a UNIQUE constraint and why does INSERT fail?

UNIQUE constraint = no two rows can have same value in that column.

From article 03: Teacher table has EmployeeCode VARCHAR(20) NOT NULL UNIQUE.

INSERT INTO Teacher (Name, EmployeeCode, Qualification)
VALUES ('Dr. Mehta', 'EMP001', 'M.Sc');
-- Success

INSERT INTO Teacher (Name, EmployeeCode, Qualification)
VALUES ('Another Teacher', 'EMP001', 'B.Ed');
-- Error: UNIQUE constraint violation
-- EMP001 already exists

Fix: Use different EmployeeCode.

INSERT INTO Teacher (Name, EmployeeCode, Qualification)
VALUES ('Another Teacher', 'EMP004', 'B.Ed');
-- Success

Why it matters: Prevents duplicate employee codes, email duplicates, etc.

🎯 Q6: How do I insert multiple rows efficiently?

Option 1: Multi-row VALUES (best)

INSERT INTO Student (Name, RollNumber, ClassName, Section)
VALUES 
    ('Ravi', 101, '10-A', 'A'),
    ('Priya', 102, '10-A', 'A'),
    ('Arjun', 103, '10-B', 'B');

One query, three rows, fast.

Option 2: INSERT...SELECT (bulk import)

INSERT INTO Student (Name, RollNumber, ClassName, Section)
SELECT Name, RollNumber, ClassName, Section FROM TempStudents;

Import thousands from another table.

Option 3: Multiple separate INSERTs (slow)

INSERT INTO Student (Name, RollNumber, ClassName) VALUES ('Ravi', 101, '10-A');
INSERT INTO Student (Name, RollNumber, ClassName) VALUES ('Priya', 102, '10-A');
INSERT INTO Student (Name, RollNumber, ClassName) VALUES ('Arjun', 103, '10-B');

Three queries, slower, avoid for bulk.

Best practice: Use multi-row VALUES for <100 rows, INSERT...SELECT for bulk.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Inserting data with INSERT INTO in SQL Server. Try these prompts:

• "Explain INSERT syntax step-by-step using the Student table example"
• "What's the difference between INSERT with column names vs without?"
• "Can I insert data without providing all columns? Give examples."
• "How do I insert multiple rows at once in one query?"
• "Quiz me: ask 5 questions about INSERT, IDENTITY, UNIQUE constraints"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

05. Querying Data -- SELECT & WHERE ->