Once you've created a table in your Microsoft SQL Server (MSSQL) database, the next step is inserting data into it. Python, combined with the pyodbc library, offers a powerful and flexible way to insert data into MSSQL programmatically.

This article explains how to insert single rows, multiple rows (bulk insert), and use parameterized queries — all with code snippets, best practices, and common pitfalls.

✅ Prerequisites

Before you can insert data, make sure:

• Python is installed (version 3.7 or higher)

• Microsoft SQL Server is running

• The pyodbc library is installed:

  pip install pyodbc
  

• You have a database and table ready. For this example, we’ll use the following Employees table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Email NVARCHAR(100),
    Age INT,
    HireDate DATE,
    Salary DECIMAL(10, 2),
    IsActive BIT
)

Step 1: Connect to MSSQL Database

SQL Server Authentication

import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=localhost;"
    "DATABASE=EmployeeDB;"
    "UID=your_username;"
    "PWD=your_password;"
)
cursor = conn.cursor()

Step 2: Insert a Single Record

Use an INSERT INTO statement with values either directly or via parameters.

Parameterized Insert (Recommended)

insert_query = """
INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, Age, HireDate, Salary, IsActive)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
"""

data = (1, 'Alice', 'Johnson', '[email protected]', 30, '2023-10-01', 75000.00, 1)
cursor.execute(insert_query, data)
conn.commit()
print("1 row inserted successfully.")

Step 3: Insert Multiple Rows (Bulk Insert)

To improve performance when inserting many rows, use executemany().

Example: Insert Multiple Employees

employees = [
    (2, 'Bob', 'Smith', '[email protected]', 28, '2023-09-15', 62000.00, 1),
    (3, 'Carol', 'Lee', '[email protected]', 35, '2023-06-20', 88000.00, 1),
    (4, 'David', 'Nguyen', '[email protected]', 40, '2022-05-10', 99000.00, 0)
]

cursor.executemany(insert_query, employees)
conn.commit()
print(f"{cursor.rowcount} rows inserted successfully.")

Full Example: Insert Data into MSSQL Table

import pyodbc

# Connection settings
server = 'localhost'
database = 'EmployeeDB'
username = 'your_username'
password = 'your_password'

try:
    # Connect to the database
    conn = pyodbc.connect(
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={server};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password};"
    )
    cursor = conn.cursor()

    # Define SQL query
    insert_query = """
    INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, Age, HireDate, Salary, IsActive)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """

    # Single record
    single_employee = (1, 'Alice', 'Johnson', '[email protected]', 30, '2023-10-01', 75000.00, 1)
    cursor.execute(insert_query, single_employee)

    # Multiple records
    employees = [
        (2, 'Bob', 'Smith', '[email protected]', 28, '2023-09-15', 62000.00, 1),
        (3, 'Carol', 'Lee', '[email protected]', 35, '2023-06-20', 88000.00, 1),
        (4, 'David', 'Nguyen', '[email protected]', 40, '2022-05-10', 99000.00, 0)
    ]
    cursor.executemany(insert_query, employees)

    # Commit the transaction
    conn.commit()
    print("All records inserted successfully.")

except pyodbc.Error as e:
    print("Error occurred:", e)

finally:
    cursor.close()
    conn.close()

Tips for Success

⚠️ Common Pitfalls

Conclusion

Inserting data into MSSQL using Python and pyodbc is efficient and scalable — whether you’re handling a single record or bulk data. Use parameterized queries for security, validate your data to avoid runtime errors, and commit your transactions to make sure everything is saved.

With this foundation, you're now ready to move on to reading, updating, or deleting records in MSSQL via Python.