Skip to content

bulkcopy does not respect explicit NULLs when the column has a default value. #894

Closed

Description

Describe the bug

If a nullable column in SQL Server has a default value, you can still insert a NULL by setting it explicitly in an INSERT statement.

However, with bulkcopy, there is no way to insert a NULL (or python None) into a column with a default. It will always take the default. This is unexpected, since it is different from the way INSERT works.

Since you can use the column_mappings param to "skip" columns like you can with INSERT, a column with explicit None/NULL should be treated the same as with INSERT, and be taken as NULL.

To reproduce

import mssql_python

def build_connection_string(host: str, port: int, database: str, username: str, password: str) -> str:
    return (
        f"Server={host},{port};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password};"
        "Encrypt=no;TrustServerCertificate=yes"
    )

connection_string = build_connection_string(...)

qualified_table_name = "dbo.test_defaults"

drop_table_query = f"DROP TABLE IF EXISTS {qualified_table_name}; "

create_table_query = f"""
CREATE TABLE {qualified_table_name}
(
    id    INT          NOT NULL,
    name  VARCHAR(255) NULL DEFAULT 'test',
    value INT          NULL DEFAULT 42, 
    insert_by VARCHAR(255) NULL,
)"""

raw_insert_query = f"""
INSERT INTO {qualified_table_name} (id, name, value, insert_by)
VALUES (1, 'first_row', 1, 'raw')    
, (2, NULL, 3, 'raw')  -- The NULL on name should be respected and not replaced by the default
, (3, NULL, NULL, 'raw')  -- Both NULLS should be respected and not replaced by the defaults
"""

raw_insert_query_2 = f"""
INSERT INTO {qualified_table_name} (id, name, insert_by)
VALUES 
(4, NULL, 'raw')  -- The value is not specified, so it should be replaced by the default 42
"""

raw_insert_query_3 = f"""
INSERT INTO {qualified_table_name} (id, insert_by)
VALUES  (5, 'raw') -- Name and Value are not specified so they should take the defaults
"""

select_query =f"SELECT * FROM {qualified_table_name}"

truncate_table_query = f"TRUNCATE TABLE {qualified_table_name}"

"""
Create the table. 
Insert the rows using raw SQL.  
Read the rows back, and verify that they are correct. 
They should respect the defaults and the explicit NULLs.
"""
with mssql_python.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(drop_table_query)
        cursor.execute(create_table_query)
        cursor.execute(raw_insert_query)
        cursor.execute(raw_insert_query_2)
        cursor.execute(raw_insert_query_3)
        cursor.execute(select_query)
        initial_rows = cursor.fetchall()
        print("Results of raw insert")
        for row in initial_rows:
            print(row)

"""
Truncate the table, then insert the rows using bulk copy.
Read the rows back, and verify that they are correct.
They should be the same!
"""
with mssql_python.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(truncate_table_query)

with mssql_python.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        # bulk copy seems to require its own connection
        cursor.bulkcopy(qualified_table_name, initial_rows, timeout=90)
        
with mssql_python.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(select_query)
        new_rows = cursor.fetchall()
        print("Results of bulk copy")
        for row in new_rows:
            print(row)

"""
Cleanup: Drop the table.
"""
with mssql_python.connect(connection_string) as conn:
    with conn.cursor() as cursor:
        cursor.execute(drop_table_query)

print("Comparison:")    
for old_row, new_row in zip(initial_rows, new_rows):
    equal = old_row == new_row
    print(f"{equal=} {old_row=} {new_row=}")
Results of raw insert
(1, 'first_row', 1, 'raw')
(2, None, 3, 'raw')
(3, None, None, 'raw')
(4, None, 42, 'raw')
(5, 'test', 42, 'raw')
Results of bulk copy
(1, 'first_row', 1, 'raw')
(2, 'test', 3, 'raw')
(3, 'test', 42, 'raw')
(4, 'test', 42, 'raw')
(5, 'test', 42, 'raw')
Comparison:
equal=True old_row=(1, 'first_row', 1, 'raw') new_row=(1, 'first_row', 1, 'raw')
equal=False old_row=(2, None, 3, 'raw') new_row=(2, 'test', 3, 'raw')
equal=False old_row=(3, None, None, 'raw') new_row=(3, 'test', 42, 'raw')
equal=False old_row=(4, None, 42, 'raw') new_row=(4, 'test', 42, 'raw')
equal=True old_row=(5, 'test', 42, 'raw') new_row=(5, 'test', 42, 'raw')

Expected behavior

bulkcopy should treat None/NULL in the same way that INSERT does.

Further technical details

Python version: 3.12
SQL Server version: Azure SQL Database
Operating system: Windows 11
mssql_python: 1.17.0

Activity

  1. github-actions commented on Nov 12, 2026

    @github-actions

    Hi Jonathan Schuba (@jschuba), thank you for opening this issue!

    Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

    If you have additional information to share, please feel free to update the issue.

    Thank you for your patience!

  2. jahnvi480 commented on Nov 13, 2026

    @jahnvi480
    Contributor

    Hi Jonathan Schuba (@jschuba) , thanks for the detailed repro! bulkcopy defaults to keep_nulls=False, which allows column defaults to replace incoming None values. Unlike regular INSERT, this follows SQL Server’s bulk-import behavior.

    To preserve explicit NULLs, please pass keep_nulls=True, which is available in version 1.17.0:

    cursor.bulkcopy(qualified_table_name, initial_rows, timeout=90, keep_nulls=True)

    Could you retry your repro with this option and let us know whether it produces the expected results?

  3. jschuba commented on Nov 16, 2026

    @jschuba
    Author

    Thank you! I added keep_nulls=True and I get the behavior I expected.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Labels

area: bulk-copyIssues in cursor.bulkcopy()discussiontriage doneIssues that are triaged by dev team and are in investigation.

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions