How to load data with COPY INTO in Azure Synapse

João Barros 10 de July de 2026 4 min read

The COPY INTO command is the fastest and simplest way to load CSV or Parquet files from your Data Lake into a table in a Dedicated SQL Pool in Azure Synapse. In a single T-SQL statement you replace complex pipelines and get high-throughput ingestion, with no extra resources to provision. You will learn to prepare the target table, write the COPY INTO command, and validate the loaded data.

Prerequisites

An active Dedicated SQL Pool in your Azure Synapse workspace.
Data files (CSV or Parquet) in an Azure Data Lake Storage Gen2 or Blob Storage account.
Write permissions on the database and read access to the storage (for example, through a Managed Identity).
A SQL editor: Synapse Studio, Azure Data Studio, or SQL Server Management Studio (SSMS).

Step 1: Create the target table

Before loading data, you need a table whose columns match those of the file, in the same order. Choose data types carefully: columns that are too wide waste memory and hurt performance in the Dedicated SQL Pool. In this example we use DISTRIBUTION = ROUND_ROBIN, which spreads rows evenly — a good choice for staging tables — and a CLUSTERED COLUMNSTORE INDEX, ideal for large analytical volumes.

CREATE TABLE dbo.Sales
(
    SaleID      INT            NOT NULL,
    SaleDate    DATE           NOT NULL,
    Product     NVARCHAR(100)  NOT NULL,
    Quantity    INT            NOT NULL,
    Amount      DECIMAL(10,2)  NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
);

Step 2: Set up storage access

COPY INTO needs authentication to read the files. The simplest and most secure option is the workspace Managed Identity. Just grant the workspace the Storage Blob Data Contributor role on the storage account, in the Azure portal. That way you manage no keys or secrets inside your code.

Tip: the Managed Identity avoids storing storage keys in your SQL. It is the recommended approach in production environments.

Step 3: Run COPY INTO

With the table created and permissions granted, run the command. This example loads every Parquet file from a Data Lake folder:

COPY INTO dbo.Sales
FROM 'https://mystorageaccount.dfs.core.windows.net/data/sales/'
WITH
(
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

When you point to a folder, COPY INTO reads every file inside it; for a single file, give the full path. If your data is in CSV with a header, set the file type and skip the first row:

COPY INTO dbo.Sales
FROM 'https://mystorageaccount.dfs.core.windows.net/data/sales.csv'
WITH
(
    FILE_TYPE = 'CSV',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0A'
);

FIRSTROW = 2 skips the header row; FIELDTERMINATOR sets the column separator and ROWTERMINATOR the end of line.

Step 4: Handle error rows

Real-world files almost always contain unexpected values. Use the ERRORFILE option so rejected rows are saved instead of failing the whole load:

COPY INTO dbo.Sales
FROM 'https://mystorageaccount.dfs.core.windows.net/data/sales.csv'
WITH
(
    FILE_TYPE = 'CSV',
    FIRSTROW = 2,
    MAXERRORS = 10,
    ERRORFILE = 'https://mystorageaccount.dfs.core.windows.net/rejected/'
);

With MAXERRORS = 10, the load continues for up to 10 invalid rows; beyond that, it fails. The problematic rows are stored in the folder set in ERRORFILE, so you can analyse them later.

Verify the result

After running the command, check how many rows landed in the table:

SELECT COUNT(*) AS TotalRows FROM dbo.Sales;

Compare the number with the expected total from the files. You can also peek at a few records with SELECT TOP 10 * FROM dbo.Sales; to confirm the columns lined up and the types are correct. If you see a conversion error, review the table types or the CSV separator.

Conclusion

With COPY INTO you get a fast, secure ingestion method with no extra infrastructure to feed your Dedicated SQL Pool. From here, you can schedule this command inside a Synapse Pipeline to automate daily loads, or combine it with a staging table before applying transformations. Have you thought about which column of your file would make the best distribution key?