The API returns a page of JSON, and the first INSERT looks easy. Loading data from an API becomes harder when page two fails and the source changes before the retry.
Split the HTTP Fetch From Loading Data From an API
Keep the HTTP client outside T-SQL. A small application or PowerShell job can handle authentication, rate limits, response codes, and retry delays. SQL Server then receives a response body and metadata through a controlled staging interface. This separation keeps database permissions narrow and makes network failures easier to diagnose.
I ask for the API’s paging and change contract before creating a target table. Does it return a next page token, a page number, or a time cursor? Can records change while paging runs? Can the same record appear twice? The answers decide how to checkpoint a run.
Save the raw response for at least the processing and recovery window. If parsing fails after a successful request, you should be able to replay the body without calling the API again. A JSON response is source evidence, not just a temporary string.
Store a Receipt for Each Page
Give every fetched page a run ID, page key, request time, HTTP status, and processing status. Use a unique constraint on run ID and page key so a retry cannot create duplicate receipts. Store the provider’s request identifier when supplied. That identifier helps the source team trace a failure.
A successful HTTP response does not mean a valid business page. Validate JSON syntax, the expected collection path, required fields, and any advertised record count. Mark a page accepted only after target rows are committed. If page processing fails, leave the receipt available for another attempt.
I prefer page keys supplied by the API over invented page numbers when the API offers a cursor. Cursor tokens can be opaque. Treat them as values to save and return, not as numbers to decode. A parser that guesses at a token’s meaning tends to fail at the least convenient time.
DECLARE @RunId bigint = 1;
SELECT PageId, RunId, PageKey, HttpStatus, ProcessedAt
FROM dbo.ApiPageReceipt
WHERE RunId = @RunId
ORDER BY PageId;Parse JSON With an Explicit Schema
OPENJSON can turn an array into rows. Use a WITH clause to name fields and data types rather than pulling every value as loose text. This makes required conversions visible. It also gives you a place to reject an unexpected shape instead of letting an empty result pass as a successful import.
The following pattern assumes the response has an items array. Your procedure should check ISJSON and the collection path before inserting. If the API uses nested objects, extract the specific path. Keep source IDs as source IDs, even when the target has its own surrogate key.
Do not discard fields that help with troubleshooting. Source update time, version, and page key can explain why a row changed. I stage those columns before deciding which fields belong in the reporting table.
DECLARE @payload nvarchar(max) =
N'{"items":[{"id":"A1","amount":12.50,"updatedAt":"2025-01-01T10:00:00"}]}';
IF ISJSON(@payload) <> 1
THROW 50000, 'Invalid JSON response.', 1;
SELECT SourceId, Amount, UpdatedAt
FROM OPENJSON(@payload, '$.items')
WITH (
SourceId nvarchar(100) '$.id',
Amount decimal(18,2) '$.amount',
UpdatedAt datetime2(0) '$.updatedAt'
);
Retry Requests Without Repeating Writes When Loading Data From an API
Retry only failures the API identifies as transient, such as a timeout or a rate limit response. Use bounded exponential backoff with jitter in the HTTP client. Honor a Retry-After header when the provider supplies one. Bad credentials and malformed requests need correction, not ten more identical calls.
The database apply step needs its own replay behavior. Upsert by a stable source key, or insert into a target protected by a unique constraint. A page can be fetched twice after an uncertain network result. The second application must leave the target correct. That is the point of idempotency.
Keep fetch retry and database retry separate in the log. A network timeout, JSON validation error, and unique key conflict call for different fixes. I have seen one generic “retry failed” message hide all three. It saves no time when somebody must investigate the run.
Respect Paging and a Changing Source
Page numbers are fragile when new records can be inserted ahead of the current page. A cursor or stable sort key is safer if the API supports it. If the provider guarantees a snapshot token, save that token with the run. If it offers only offsets, document the risk and reconcile the final target against a later full extract.
Stop when the API’s documented end condition is reached, not when a page happens to contain fewer rows than expected. Some APIs return short pages before the end. Store the next cursor only after the current page is durable. On restart, read the saved cursor from the last accepted receipt.
What happens when a record changes halfway through the run? If the API provides a modified-since filter, use an overlap and an idempotent target apply. If it provides versioned snapshots, use them. Otherwise, schedule a reconciliation pass. The source contract defines what “complete” can mean.
Checkpoint the Target Apply
A page receipt and its target changes should share a transaction when they live in the same database. Begin the transaction, apply validated rows, record accepted and rejected counts, mark the page committed, and commit. A failure before commit leaves the page ready for replay. A failure after commit finds an already accepted receipt.
Avoid advancing a global cursor before all rows in the page have passed validation. If bad records need review, choose whether the whole page fails or accepted rows can commit with a reject table. Document that choice. Silent partial success is an expensive surprise.
A query against pending receipts gives an operator a starting point. It does not require opening an application log first. Keep the status names few and precise, such as fetched, validated, committed, and failed. A status called “done” says remarkably little.
SELECT RunId, PageKey, HttpStatus, ProcessingStatus, ErrorMessage
FROM dbo.ApiPageReceipt
WHERE ProcessingStatus IN ('Fetched', 'Failed')
ORDER BY RunId, PageId;Test the Failure Paths for Loading Data From an API
Test a timeout before a page arrives, an invalid JSON body, a duplicate source ID, and a failure after target rows commit. These are different recovery paths. A run that succeeds once with a three page sample has not shown that it can restart.
Check row counts and source IDs after replay. Confirm that the last committed cursor is the one the next request uses. Verify that the raw response and request metadata remain available long enough for support. I also check that credentials never land in a receipt, error message, or query text.
The best process for loading data from an API is boring on its second run and clear on its bad day. SQL Server should receive validated, traceable pages. The HTTP client should own network behavior. Together, those parts can resume without pretending a failed page never happened.
Related reading on this blog: 2016: Check Value as JSON With ISJSON() and SQL SERVER Performance: JSON vs XML.
An API load is not a loop over pages, it is a recoverable conversation with a changing source.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
