# Replicating SQL Server tables to MotherDuck


> Replicate SQL Server tables to MotherDuck using Python and dataframes.

This page will serve to show basic patterns for using Python to connect to SQL Server, read data into a dataframe, connect to MotherDuck, and then writing the data from the dataframe into MotherDuck. For more complex replication scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion).

To skip the documentation and look at the entire script, expand the element below:

<details>
    <summary>Python script</summary>
    ```py
    import pyodbc

    # Define your connection parameters
    server = 'ip_address'
    database = 'master'         # or use your database name
    username = 'your_username'
    password = 'your_password'  # consider using a secret manager or .env
    port = 1433  # default SQL Server port

    # Define the connection string for ODBC Driver 17
    connection_string = (
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={server},{port};"
        f"DATABASE={database};"
        f"UID={username};"
        f"PWD={password};"
    )

    # Connect to SQL Server
    try:
        connection = pyodbc.connect(connection_string)
        print("Connection successful.")
    except pyodbc.Error as e:
        print(f"Error: {e}")
    finally:
        connection.close()

    import pandas as pd

    try:
        connection = pyodbc.connect(connection_string)
        query = "SELECT * FROM AdventureWorks2022.Production.BillOfMaterials"

        # Execute the query using pyodbc
        cursor = connection.cursor()
        cursor.execute(query)

        # Fetch the column names and data
        columns = [column[0] for column in cursor.description]
        data = cursor.fetchall()

        # Convert the data into a DataFrame
        df = pd.DataFrame.from_records(data, columns=columns)
    finally:
        connection.close()

    import duckdb

    motherduck_token = 'your_token'

    # Attach using the MOTHERDUCK_TOKEN
    duckdb.sql(f"ATTACH 'md:my_db?MOTHERDUCK_TOKEN={motherduck_token}'")

    # Create or replace table in the attached database
    duckdb.sql(
        """
        CREATE OR REPLACE TABLE my_db.main.BillOfMaterials AS
        SELECT * FROM df
        """
    )
    ```
</details>

## SQL Server authentication

SQL Server supports [multiple methods of authentication](https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver16) - for the purpose of this example, we will use username/password authentication and [pyodbc](https://github.com/mkleehammer/pyodbc/), along with [ODBC Driver 17 for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16). 'ODBC Driver 18 for SQL Server' is also available and supports some newer SQL Server features, but this example uses 17 for compatibility.

Consider the following authentication example:

```py
import pyodbc

# Define your connection parameters
server = 'ip_address'
database = 'master'         # or use your database name
username = 'your_username'
password = 'your_password'  # consider using a secret manager or .env
port = 1433  # default SQL Server port

# Define the connection string for ODBC Driver 17
connection_string = (
    f"DRIVER={{ODBC Driver 17 for SQL Server}};"
    f"SERVER={server},{port};"
    f"DATABASE={database};"
    f"UID={username};"
    f"PWD={password};"
)

# Connect to SQL Server
try:
    connection = pyodbc.connect(connection_string)
    print("Connection successful.")
except pyodbc.Error as e:
    print(f"Error: {e}")
finally:
    connection.close()
```

This will set your credentials, and then attempt to connect to your server with `pyodbc.connect`, and return an error if it fails.

## Reading a SQL Server table into a dataframe

Once you have authenticated, you can define arbitrary queries and then execute them with `pd.read_sql`, using the `query` and `connection` objects. For the purpose of this example, we are using SQL Server 2022 along with the AdventureWorks OLTP database.

:::note
While `pandas` is a great library, it is not particularly well-suited for very large tables. To learn more about using buffers and alternative libraries, check out [Loading data with Python](/key-tasks/loading-data-into-motherduck/loading-data-md-python/).
:::

```py
import pandas as pd

try:
    connection = pyodbc.connect(connection_string)
    query = "SELECT * FROM AdventureWorks2022.Production.BillOfMaterials"

    # Execute the query using pyodbc
    cursor = connection.cursor()
    cursor.execute(query)

    # Fetch the column names and data
    columns = [column[0] for column in cursor.description]
    data = cursor.fetchall()

    # Convert the data into a DataFrame
    df = pd.DataFrame.from_records(data, columns=columns)
finally:
    connection.close()
```

## Inserting the table into MotherDuck

Now that the data has been loaded into a dataframe object, we can connect to MotherDuck and insert the table.

:::note
You will need to [generate a token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token) in your MotherDuck account. For production use cases, use a secret manager and never commit your token to your codebase.
:::

```py
import duckdb

motherduck_token = 'your_token'

# Attach using the MOTHERDUCK_TOKEN
duckdb.sql(f"ATTACH 'md:my_db?MOTHERDUCK_TOKEN={motherduck_token}'")

# Create or replace table in the attached database
duckdb.sql(
    """
    CREATE OR REPLACE TABLE my_db.main.BillOfMaterials AS
    SELECT * FROM df
    """
)
```

This will create the table, or replace it for the table already exists.

## Handling more complex workflows

Production use cases tend to be much more complex and include things like incremental builds & state management. In those scenarios, please take a look at our [ingestion partners](https://motherduck.com/ecosystem/?category=Ingestion), which includes many options including some that offer native python. An overview of the MotherDuck Ecosystem is shown below.

Diagram summary: the MotherDuck ecosystem. Sources and ingestion tools load data into MotherDuck; BI tools, notebooks, and applications query it back out; transformation, orchestration, and data quality tools work on it. Each lane below is a sample, not the full list.

Loading data in:
- **Databases**: PostgreSQL, MySQL, SQL Server, Snowflake, BigQuery, MongoDB (/integrations/databases/)
- **Files and object storage**: Amazon S3, Cloudflare R2, Azure Blob, Parquet, Apache Iceberg, DuckLake (/integrations/cloud-storage/)
- **Ingestion**: dltHub, Fivetran, Airbyte, Estuary, Sling, Apache Kafka, Salesforce, Stripe (/integrations/ingestion/)

Reading data out:
- **Business intelligence**: Power BI, Tableau, Metabase, Hex, Omni, Superset, Rill Data, Evidence (/integrations/bi-tools/)
- **Data science and AI**: Python, Jupyter, Marimo, Google Colab, LangChain, LlamaIndex (/integrations/data-science-ai/)
- **Apps and developer tools**: Node.js, Go Driver, JDBC, Rust, Streamlit, Retool, Cloudflare Workers, DBeaver (/integrations/language-apis-and-drivers/)

Working on the warehouse:
- **Transformation**: dbt core, dbt cloud, SQLMesh, Paradime (/integrations/transformation/)
- **Orchestration**: Airflow, Dagster, Prefect, Kestra, Bacalhau, Astronomer (/integrations/orchestration/)
- **Data quality**: Great Expectations, Monte Carlo, Soda, Secoda (/integrations/data-quality/)

What MotherDuck brings in the middle:
- **Ducklings**: Compute, one per account (/concepts/hypertenancy/)
- **Storage**: Databases, snapshots, catalog (/concepts/storage-lifecycle/)
- **Dual Execution**: Query cloud and local data together (/concepts/architecture-and-capabilities/#dual-execution)
- **Flights**: Python on a schedule (/concepts/flights/)
- **Dives**: SQL and React data apps (/key-tasks/dives/)
- **Guides**: Context your agents read (/key-tasks/guides/)
- **MCP server**: Agent access to your data (/sql-reference/mcp/)
- **Shares**: Read access without copying (/key-tasks/sharing-data/)

The full list is at /integrations/.


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fdata-warehousing%2Freplication%2Fsql-server%2F&page_title=SQL%20Server&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.