Loading Data into SQL Server 2019: Every API and Method Explained

---

SQL Server offers more ways to load data than most people realize. BULK INSERT and BCP are the ones everyone knows. But depending on your source format, your volume requirements, your application stack, and whether you need transformation during load, one of the other options might be significantly better for your specific situation.

This article covers every practical method for loading data into an on-premises SQL Server 2019 instance: what each one does, when it is the right choice, the performance characteristics to expect, and the T-SQL or command-line syntax to use. SQL Server 2019 specific considerations are called out throughout since some features available in Azure SQL and SQL Server 2025 are not available here.

1 Choosing the Right Method: The Decision Table Beginner

Method	Best For	Typical Speed	Who Uses It
BULK INSERT	Large flat file loads from disk or network share	Very fast (minimal logging)	DBAs, ETL
BCP	Command-line bulk export or import, scripted pipelines	Very fast	DBAs, sysadmins
OPENROWSET BULK	Ad hoc file queries without staging, single-file imports	Fast	DBAs, developers
SqlBulkCopy (.NET)	Application-generated data, high-volume API ingestion	Very fast	.NET developers
Table-Valued Parameters	Sending sets of rows from application in one call	Moderate (per-call overhead)	.NET developers
PolyBase	External data sources: Hadoop, Azure Blob, other SQL	Varies by source	Data engineers
Linked Servers	Cross-instance data movement, Oracle/PostgreSQL sources	Moderate	DBAs
SSIS	Complex ETL with transformations, scheduling, logging	Fast with proper config	ETL developers
MERGE	Upsert: insert new, update existing rows	Slower than bulk (row-level)	DBAs, developers
REST API ingestion	Receiving data from external APIs	Depends on approach	Developers, DBAs

2 BULK INSERT: The Workhorse Beginner

BULK INSERT is the T-SQL statement for loading data from a flat file directly into a SQL Server table. It has been in the product since SQL Server 7 and it remains the fastest way to load large volumes of data from a file. SQL Server 2017 added native CSV support which eliminated the need for format files in most straightforward cases.

-- Basic CSV load (SQL Server 2017 and later, no format file needed)
BULK INSERT dbo.SalesOrders
FROM 'C:\Data\orders_2026.csv'
WITH (
    FORMAT          = 'CSV',
    FIRSTROW        = 2,        -- skip header row
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    TABLOCK,                    -- table lock for fastest load
    BATCHSIZE       = 50000,    -- commit every 50k rows
    MAXERRORS       = 10        -- allow up to 10 errors before aborting
);

-- Check how many rows loaded
SELECT COUNT(*) FROM dbo.SalesOrders;

-- Load with an error file to capture rejected rows
BULK INSERT dbo.SalesOrders
FROM 'C:\Data\orders_2026.csv'
WITH (
    FORMAT          = 'CSV',
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    ERRORFILE       = 'C:\Data\orders_errors.txt',
    MAXERRORS       = 100,
    TABLOCK
);

-- Load a tab-delimited file
BULK INSERT dbo.ProductInventory
FROM 'C:\Data\inventory.txt'
WITH (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2,
    TABLOCK
);

-- Load only specific columns (source file has more columns than target table)
-- Use a format file for column mapping
-- Generate a format file with BCP:
-- bcp YourDB.dbo.SalesOrders format nul -c -f C:\Data\orders.fmt -S . -T

When to Use BULK INSERT

• Loading CSV, TSV, or fixed-width flat files that are already on a disk path accessible to the SQL Server service account
• Large volume loads where minimal logging and maximum speed are required
• Scheduled ETL jobs that drop files to a network share and process them on a schedule
• Any scenario where you want the load logic inside T-SQL without an external tool dependency

3 OPENROWSET BULK: Query Files Like Tables Beginner

OPENROWSET with the BULK option lets you reference a file in the FROM clause of a SELECT statement as if it were a table. This means you can query, filter, transform, and join file data before inserting it, which BULK INSERT does not support directly.

-- Enable ad hoc distributed queries (required for OPENROWSET)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

-- Query a CSV file with OPENROWSET BULK
SELECT *
FROM OPENROWSET(
    BULK 'C:\Data\orders_2026.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
) WITH (
    OrderID     INT,
    CustomerID  INT,
    OrderDate   DATE,
    OrderTotal  DECIMAL(18,2)
) AS FileData;

-- Insert with transformation (filter before loading)
INSERT INTO dbo.SalesOrders (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM OPENROWSET(
    BULK 'C:\Data\orders_2026.csv',
    FORMAT = 'CSV',
    FIRSTROW = 2
) WITH (
    OrderID     INT,
    CustomerID  INT,
    OrderDate   DATE,
    OrderTotal  DECIMAL(18,2),
    Status      NVARCHAR(50)    -- read this column
) AS FileData
WHERE Status = 'Completed'      -- filter during load
AND   OrderTotal > 0;

-- Load a single file as a BLOB (e.g., store a document)
INSERT INTO dbo.DocumentStore (DocName, DocContent)
SELECT 'contract_2026.pdf',
       BulkColumn
FROM OPENROWSET(BULK 'C:\Documents\contract_2026.pdf',
               SINGLE_BLOB) AS DocData;

BULK INSERT vs OPENROWSET BULK

Use BULK INSERT when you want a simple load with no transformation. Use OPENROWSET BULK when you need to filter, join, or transform the data before it enters the table. Both use the same underlying bulk load engine and deliver similar performance.

4 INSERT SELECT and Regular INSERT Beginner

Standard INSERT statements are fully logged and process rows one at a time or in small sets. They are the right choice for small volumes, application-level inserts, and situations where full logging and constraint checking on every row is required. For high-volume loads they are significantly slower than bulk methods.

-- Standard single-row INSERT
INSERT INTO dbo.Orders (CustomerID, OrderDate, OrderTotal, OrderStatusID)
VALUES (1042, SYSDATETIME(), 149.99, 2);

-- INSERT SELECT from another table (fully logged)
INSERT INTO dbo.OrderArchive (OrderID, CustomerID, OrderDate, OrderTotal)
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM dbo.Orders
WHERE OrderDate < '2025-01-01'
AND   OrderStatusID = 5;  -- Delivered orders older than 2025

-- SELECT INTO: creates new table and populates it in one operation
-- Minimally logged in simple recovery or with trace flag 610
SELECT OrderID, CustomerID, OrderDate, OrderTotal
INTO dbo.OrderArchive_2024
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2024;

-- Note: SELECT INTO is minimally logged in SIMPLE recovery model
-- and BULK_LOGGED recovery model with the right conditions
-- In FULL recovery it is fully logged

5 MERGE: Upsert Patterns Beginner

MERGE combines INSERT, UPDATE, and DELETE in one statement based on whether source rows match target rows. It is the SQL Server upsert pattern: insert rows that do not exist, update rows that do. For data loading scenarios where you cannot know in advance whether incoming rows are new or updates, MERGE handles both cases in one pass.

-- MERGE: upsert pattern for loading staging data into production
MERGE dbo.ProductInventory AS Target
USING dbo.ProductInventory_Staging AS Source
    ON Target.ProductID = Source.ProductID

WHEN MATCHED AND (
    Target.StockQuantity <> Source.StockQuantity OR
    Target.LastUpdated    <> Source.LastUpdated
) THEN
    UPDATE SET
        Target.StockQuantity = Source.StockQuantity,
        Target.LastUpdated   = Source.LastUpdated

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, ProductName, StockQuantity, LastUpdated)
    VALUES (Source.ProductID, Source.ProductName,
            Source.StockQuantity, Source.LastUpdated)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE  -- remove products no longer in the source

OUTPUT $action, Inserted.ProductID, Deleted.ProductID
INTO dbo.MergeAuditLog;  -- capture what changed

6 BCP: The Original Bulk Copy Utility Beginner

BCP (Bulk Copy Program) is the command-line utility that has been part of SQL Server since version 1.0. It runs outside SQL Server as a standalone executable and uses its own ODBC-based bulk copy protocol. BCP can both export data from SQL Server to a file and import data from a file into SQL Server.

-- BCP IMPORT: load data from file into table
bcp YourDatabase.dbo.SalesOrders IN "C:\Data\orders.csv" ^
    -c ^           -- character mode (text)
    -t "," ^       -- field terminator
    -r "\n" ^      -- row terminator
    -F 2 ^         -- first row (skip header)
    -S YourServer ^
    -T             -- trusted (Windows auth)

-- BCP EXPORT: extract data from table to file
bcp YourDatabase.dbo.SalesOrders OUT "C:\Data\orders_export.csv" ^
    -c ^
    -t "," ^
    -r "\n" ^
    -S YourServer ^
    -T

-- BCP with a query instead of a whole table
bcp "SELECT OrderID, CustomerID, OrderTotal FROM YourDatabase.dbo.SalesOrders WHERE OrderDate >= '2026-01-01'" ^
    QUERYOUT "C:\Data\orders_q1_2026.csv" ^
    -c -t "," -r "\n" ^
    -S YourServer -T

-- Generate a format file (use for complex column mappings)
bcp YourDatabase.dbo.SalesOrders format nul ^
    -c ^
    -f "C:\Data\orders.fmt" ^
    -S YourServer -T

BCP vs BULK INSERT

BCP runs as a command-line process outside SQL Server and uses ODBC. BULK INSERT runs inside SQL Server and uses OLE DB. For most practical purposes their performance is similar on large files. BCP is easier to automate in shell scripts. BULK INSERT is easier to integrate into T-SQL stored procedures and SQL Agent jobs. For SQL Server 2019 both are fully supported and production-ready.

7 SqlBulkCopy: The .NET Bulk Load API Intermediate

SqlBulkCopy is the .NET API for bulk loading data into SQL Server from an application. It uses the same TDS bulk load protocol as BCP and BULK INSERT under the hood, which means it achieves similar performance while running entirely within application code. It is the right choice when your application generates data programmatically and you need to push large volumes into SQL Server efficiently.

// C# SqlBulkCopy example: load a DataTable into SQL Server
// Achieves similar performance to BULK INSERT for large datasets

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (var bulkCopy = new SqlBulkCopy(connection,
        SqlBulkCopyOptions.TableLock |          // table lock for speed
        SqlBulkCopyOptions.FireTriggers |       // fire triggers if needed
        SqlBulkCopyOptions.UseInternalTransaction, // transaction per batch
        null))
    {
        bulkCopy.DestinationTableName = "dbo.SalesOrders";
        bulkCopy.BatchSize            = 10000;     // rows per batch
        bulkCopy.BulkCopyTimeout      = 300;       // 5 minute timeout

        // Map source columns to destination columns
        // (required when column names or order differ)
        bulkCopy.ColumnMappings.Add("source_order_id",    "OrderID");
        bulkCopy.ColumnMappings.Add("source_customer_id", "CustomerID");
        bulkCopy.ColumnMappings.Add("order_date",         "OrderDate");
        bulkCopy.ColumnMappings.Add("total_amount",       "OrderTotal");

        // dataTable is a DataTable populated from your data source
        await bulkCopy.WriteToServerAsync(dataTable);
    }
}

// SqlBulkCopy also accepts:
// - IDataReader (stream rows from any source without materializing all into memory)
// - DataRow[] array
// - DbDataReader

// For very large datasets use IDataReader to avoid holding
// the entire dataset in memory before loading

8 Table-Valued Parameters: Passing Sets from Applications Intermediate

Table-Valued Parameters (TVPs) let an application pass an entire set of rows to a stored procedure in a single database call. Instead of making one INSERT call per row, the application populates a table variable on the client side and passes the whole set to SQL Server in one round trip. TVPs are ideal for transactional batch inserts from applications where you need constraint checking and stored procedure logic on the entire set.

-- Step 1: Create the table type in SQL Server
CREATE TYPE dbo.OrderItemTableType AS TABLE (
    OrderID         INT             NOT NULL,
    ProductID       INT             NOT NULL,
    Quantity        INT             NOT NULL,
    UnitPrice       DECIMAL(18,2)   NOT NULL
);

-- Step 2: Create a stored procedure that accepts the TVP
CREATE PROCEDURE dbo.usp_InsertOrderItems
    @OrderItems dbo.OrderItemTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.OrderItems (OrderID, ProductID, Quantity, UnitPrice)
    SELECT OrderID, ProductID, Quantity, UnitPrice
    FROM @OrderItems;

    -- Can join TVP data with other tables for validation
    -- before inserting, all in one transaction
END;

-- Step 3: Call from C# with SqlBulkCopy-like efficiency
// C# - pass a DataTable as TVP parameter
var param = new SqlParameter("@OrderItems", SqlDbType.Structured);
param.TypeName = "dbo.OrderItemTableType";
param.Value    = orderItemsDataTable;  // DataTable with matching schema

command.Parameters.Add(param);
command.ExecuteNonQuery();

SqlBulkCopy vs TVPs

SqlBulkCopy bypasses stored procedures and inserts directly, which is faster for raw volume. TVPs go through stored procedures, which allows validation logic, triggers, and transactional integrity checks on the full set. Use SqlBulkCopy for maximum throughput on clean data. Use TVPs when you need business logic applied during the load.

9 ODBC Bulk Copy API Intermediate

The ODBC bulk copy API is a lower-level C/C++ API that provides the same bulk load capability as BCP from native code. It is the protocol BCP itself uses. For applications written in C or C++ that need maximum bulk load performance this is the lowest-level option available. Most modern applications use SqlBulkCopy (.NET) or the equivalent driver-level APIs in other languages instead.

Python developers can access SQL Server bulk load through pyodbc's fast_executemany option or through the mssql-python driver's bulk copy support, both of which use the ODBC layer under the hood.

# Python: bulk load using pyodbc fast_executemany
# Significantly faster than standard executemany for large datasets

import pyodbc

conn = pyodbc.connect(
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=YourServer;"
    "DATABASE=YourDatabase;"
    "Trusted_Connection=yes;"
)
conn.autocommit = False
cursor = conn.cursor()

# Enable fast_executemany for bulk-like performance
cursor.fast_executemany = True

data = [
    (1, 1042, '2026-06-01', 149.99),
    (2, 1087, '2026-06-01', 299.50),
    # ... thousands more rows
]

cursor.executemany(
    "INSERT INTO dbo.SalesOrders (OrderID, CustomerID, OrderDate, OrderTotal) "
    "VALUES (?, ?, ?, ?)",
    data
)
conn.commit()

10 PolyBase: External Data Sources and Virtual Tables Intermediate

PolyBase lets SQL Server 2019 query external data sources including Hadoop, Azure Blob Storage, Azure Data Lake Storage, Oracle, MongoDB, Teradata, and other SQL Server instances using standard T-SQL. The data is not imported into SQL Server but queried in place. For data loading scenarios PolyBase can push the result of an external query into a SQL Server table using CREATE EXTERNAL TABLE AS SELECT or INSERT SELECT FROM an external table.

-- Enable PolyBase (requires PolyBase feature installed)
EXEC sp_configure 'hadoop connectivity', 7;
RECONFIGURE;

-- Create an external data source pointing to Azure Blob Storage
CREATE EXTERNAL DATA SOURCE AzureBlobSource
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'wasbs://container@yourstorage.blob.core.windows.net',
    CREDENTIAL = AzureStorageCredential
);

-- Create an external file format
CREATE EXTERNAL FILE FORMAT CSVFormat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2
    )
);

-- Create an external table (no data is copied to SQL Server yet)
CREATE EXTERNAL TABLE dbo.ExternalOrders (
    OrderID     INT,
    CustomerID  INT,
    OrderDate   DATE,
    OrderTotal  DECIMAL(18,2)
)
WITH (
    LOCATION    = '/orders/2026/',
    DATA_SOURCE = AzureBlobSource,
    FILE_FORMAT = CSVFormat
);

-- Query the external table (reads from blob storage live)
SELECT COUNT(*), SUM(OrderTotal)
FROM dbo.ExternalOrders
WHERE YEAR(OrderDate) = 2026;

-- Load external data into a local SQL Server table
INSERT INTO dbo.SalesOrders
SELECT * FROM dbo.ExternalOrders
WHERE OrderTotal > 0;

-- KEY POINT for SQL Server 2019:
-- PolyBase V2 in SQL Server 2019 added ODBC connectors
-- for Oracle, MongoDB, Teradata, generic ODBC sources
-- This makes SQL Server 2019 PolyBase significantly more
-- capable than SQL Server 2016 or 2017 PolyBase

11 Linked Servers: Cross-Instance Data Movement Beginner

Linked Servers let SQL Server query and load data from other database instances including other SQL Server instances, Oracle, PostgreSQL, MySQL, and any ODBC-accessible source. Data can be pulled from a linked server using a four-part name query and inserted into a local table.

-- Create a linked server to another SQL Server instance
EXEC sp_addlinkedserver
    @server     = N'RemoteSQLServer',
    @srvproduct = N'SQL Server';

EXEC sp_addlinkedsrvlogin
    @rmtsrvname  = N'RemoteSQLServer',
    @useself     = N'False',
    @locallogin  = NULL,
    @rmtuser     = N'RemoteLoginName',
    @rmtpassword = N'RemotePassword';

-- Pull data from linked server into local table
INSERT INTO dbo.SalesOrders
SELECT OrderID, CustomerID, OrderDate, OrderTotal
FROM [RemoteSQLServer].[YourDatabase].[dbo].[Orders]
WHERE OrderDate >= '2026-01-01';

-- Create a linked server to PostgreSQL via ODBC
EXEC sp_addlinkedserver
    @server     = N'PostgreSQLSource',
    @srvproduct = N'PostgreSQL',
    @provider   = N'MSDASQL',
    @provstr    = N'Driver={PostgreSQL Unicode};Server=pgserver;Database=pgdb;';

-- Query PostgreSQL through linked server
SELECT * FROM OPENQUERY(PostgreSQLSource,
    'SELECT customer_id, name, email FROM customers WHERE active = true');

12 SSIS: Orchestrated ETL Pipelines Intermediate

SQL Server Integration Services (SSIS) is the full ETL platform included with SQL Server. It handles complex data loading scenarios with multiple sources, transformations, error routing, logging, and scheduling. For straightforward file loads SSIS is heavier than needed. For multi-source, multi-destination pipelines with business logic transformations SSIS is the appropriate tool.

SSIS packages can be executed from SQL Agent jobs, from the command line with dtexec, from the SSIS Catalog on SQL Server, or from Azure Data Factory as a lift-and-shift migration path.

-- Execute an SSIS package from T-SQL via SQL Agent
-- (package stored in SSIS Catalog on SSISDB)
EXEC [SSISDB].[catalog].[create_execution]
    @package_name  = N'LoadSalesOrders.dtsx',
    @folder_name   = N'DataLoading',
    @project_name  = N'SalesETL',
    @use32bitruntime = False,
    @execution_id  = @exec_id OUTPUT;

EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    @execution_id  = @exec_id,
    @object_type   = 50,
    @parameter_name = N'SYNCHRONIZED',
    @parameter_value = 1;

EXEC [SSISDB].[catalog].[start_execution]
    @execution_id = @exec_id;

-- Check execution status
SELECT execution_id, status, start_time, end_time,
       elapsed_time_seconds
FROM [SSISDB].[catalog].[executions]
WHERE execution_id = @exec_id;

13 Receiving Data from REST APIs on SQL Server 2019 Intermediate

SQL Server 2019 does not have a native REST endpoint that external systems can POST data to. Receiving data from REST APIs requires one of three approaches. The right choice depends on your infrastructure and application stack.

Option 1: Application Layer (Recommended)

Build a small REST API in ASP.NET Core, Node.js, or Python Flask that receives the incoming data and writes it to SQL Server using SqlBulkCopy or parameterized INSERT statements. This is the cleanest architecture: SQL Server stays as a data store, the API layer handles HTTP concerns, and the two are properly separated.

Option 2: SQL Agent PowerShell Step

A SQL Agent job step of type PowerShell can call external REST APIs using Invoke-RestMethod, process the response, and load it into SQL Server. This keeps everything inside the SQL Server ecosystem without requiring an external application server.

-- SQL Agent PowerShell job step content
-- This runs on a schedule and pulls data from an external API

$ConnectionString = "Server=.;Database=YourDB;Integrated Security=True;"
$ApiUrl = "https://api.yourvendor.com/data/orders"

# Call the REST API
$Response = Invoke-RestMethod -Uri $ApiUrl `
    -Method GET `
    -Headers @{Authorization = "Bearer $ApiToken"} `
    -ContentType "application/json"

# Connect to SQL Server
$Connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$Connection.Open()

# Use SqlBulkCopy to load the response data
$BulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($Connection)
$BulkCopy.DestinationTableName = "dbo.ExternalAPIOrders"
$BulkCopy.BatchSize = 1000

# Convert API response to DataTable and load
$DataTable = New-Object System.Data.DataTable
$DataTable.Columns.Add("OrderID",    [int])
$DataTable.Columns.Add("CustomerID", [int])
$DataTable.Columns.Add("OrderDate",  [datetime])
$DataTable.Columns.Add("Amount",     [decimal])

foreach ($item in $Response.orders) {
    $Row = $DataTable.NewRow()
    $Row["OrderID"]    = $item.order_id
    $Row["CustomerID"] = $item.customer_id
    $Row["OrderDate"]  = $item.order_date
    $Row["Amount"]     = $item.amount
    $DataTable.Rows.Add($Row)
}

$BulkCopy.WriteToServer($DataTable)
$Connection.Close()

Write-Host "Loaded $($DataTable.Rows.Count) rows from API"

Option 3: SQLCLR (Use With Caution)

SQLCLR allows .NET code to run inside SQL Server. A CLR stored procedure can make HTTP calls and load the response. This approach is available on SQL Server 2019 but Microsoft has progressively tightened CLR security and the architectural pattern of SQL Server making outbound HTTP calls is generally discouraged in production. Use the application layer approach or the Agent PowerShell approach instead.

14 Minimally Logged Bulk Loads: Getting Maximum Speed Intermediate

The difference between a minimally logged bulk load and a fully logged one can be dramatic. A minimally logged load writes extent allocations to the log rather than individual row changes. For large loads this can reduce log I/O by 80 to 90 percent and significantly reduce load time.

Minimal logging applies to BULK INSERT and SELECT INTO under specific conditions:

• The database is in SIMPLE or BULK_LOGGED recovery model during the load
• The TABLOCK hint is specified on the target table
• The target table has no non-clustered indexes, or the indexes are disabled during the load
• The target table is empty (for new loads) or data is being appended to empty space

-- Check current recovery model
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name = DB_NAME();

-- For large one-time loads: temporarily switch to BULK_LOGGED
-- This enables minimal logging while preserving backup chain
ALTER DATABASE YourDatabase SET RECOVERY BULK_LOGGED;

-- Run the bulk load with TABLOCK
BULK INSERT dbo.LargeFactTable
FROM 'C:\Data\large_load.csv'
WITH (
    FORMAT      = 'CSV',
    FIRSTROW    = 2,
    TABLOCK,                -- required for minimal logging
    BATCHSIZE   = 100000
);

-- Switch back to FULL recovery immediately after the load
ALTER DATABASE YourDatabase SET RECOVERY FULL;

-- IMPORTANT: take a log backup after switching back to FULL
-- The bulk-logged operations need to be captured in a backup
BACKUP LOG YourDatabase
TO DISK = 'C:\Backups\YourDatabase_after_bulkload.trn';

-- Check transaction log growth during the load
SELECT log_reuse_wait_desc,
       log_size_mb = (SELECT SUM(size * 8.0 / 1024)
                      FROM sys.database_files
                      WHERE type_desc = 'LOG')
FROM sys.databases
WHERE name = DB_NAME();

15 Permissions Required for Each Method Beginner

Method	Minimum Permission Required	Notes
BULK INSERT	INSERT + ADMINISTER BULK OPERATIONS	Service account needs file system read access
OPENROWSET BULK	INSERT + ADMINISTER BULK OPERATIONS	Ad hoc distributed queries must be enabled
BCP (import)	INSERT + ADMINISTER BULK OPERATIONS	Runs as the Windows account executing the command
SqlBulkCopy	INSERT on target table	Plus SELECT if using SqlBulkCopyOptions.CheckConstraints
TVPs	EXECUTE on the stored procedure	No direct table permissions needed if proc handles it
PolyBase	INSERT + CONTROL on external data source	Requires PolyBase feature installed
Linked Servers	INSERT on local + remote login configured	Remote credentials stored in linked server login
SSIS	Depends on package design	Package execution requires SSIS catalog permissions
Regular INSERT	INSERT on target table	Simplest permission requirement

References

• Microsoft Docs: Import Bulk Data Using BULK INSERT or OPENROWSET(BULK)
• Microsoft Docs: BULK INSERT (Transact-SQL)
• Microsoft Docs: BCP Utility
• Microsoft Docs: SqlBulkCopy Class (.NET)
• Microsoft Docs: Use Table-Valued Parameters
• Microsoft Docs: PolyBase Guide
• Microsoft Docs: Prerequisites for Minimal Logging in Bulk Import
• SQLYARD: SQL Server and APIs: A Complete Guide (Outbound REST calls)
• SQLYARD: SQL Server Transaction Log Full Guide
• SQLYARD: SQL Server Instance Setup and Best Practices
• SQLYARD: SQL Server MERGE Guide