azuresql-db-seed

Seed the dev database on the Azure SQL Database container

Fill an existing appdb with realistic sample data so the app has something to render, query, and test against. This is the Azure SQL engine (Private Preview), not the SQL Server image.

Use this skill for populating data. For bootstrapping a whole new app use azuresql-db-scaffold; for restoring an existing .bacpac use azuresql-db-import.

Verified on 2026-09-05 against the container image sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest, reporting EngineEdition 5, Edition SQL Azure, build 12.0.2000.8. All seven executable checks behind this skill passed: the engine identity, Msg 12713 for BULK INSERT and for OPENROWSET(BULK ...) against a local file, Msg 547 for a child row inserted before its parent, OUTPUT ... INTO capturing generated identity values, the tally recipe fanning out 1000 rows, and bcp shipping in the image.

Engine facts that shape seeding

• USE this engine image: sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io/azure-sql/db-dev:latest (multi-architecture: linux/amd64 and linux/arm64). Do NOT use mcr.microsoft.com/mssql/server (the SQL Server image).
• The registry is private (Private Preview): run docker login sqldbpreview-dpgaeqhmgphzd4bk.azurecr.io first with the pull-only credentials from https://aka.ms/sqldbcontainerpreview-signup (they may rotate).
• Verify identity: SELECT SERVERPROPERTY('EngineEdition') returns 5 and SERVERPROPERTY('Edition') returns 'SQL Azure'.
• Required env when starting the container: ACCEPT_EULA=Y and a complex MSSQL_SA_PASSWORD (example literal used throughout: YourStr0ng_Passw0rd). The engine listens on 1433.
• The runtime selects the matching architecture. Use --platform linux/amd64 only to explicitly test x64 emulation.
• The engine does NOT auto-create databases. You must CREATE DATABASE appdb on a master connection before you seed anything.
• Do NOT use USE appdb to switch databases. In a user-database session USE returns Msg 40508. Always select the target database in the connection string (Database=appdb, or -d appdb for sqlcmd).
• Apps read one env var, SQL_CONNECTION_STRING. Strings use User Id= / Password= / Database= and TrustServerCertificate=true. sqlcmd uses -C to trust the self-signed cert.

Step 1: provision appdb (always, before any seed)

Seeding into a database that does not exist fails. Create appdb on a master connection first:

docker exec sqldb /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -C -b \
  -Q "IF DB_ID('appdb') IS NULL CREATE DATABASE appdb;"

If the container is not running yet, start it and provision appdb using the canonical start recipe in the azuresql-db-container skill, then come back here.

Step 2: insert in foreign-key order (parents before children)

Referential integrity is enforced. A child row whose foreign key points at a parent that does not exist yet fails with Msg 547 (conflict with the FOREIGN KEY constraint). So insert in dependency order: parents first, then the rows that reference them.

For a simple dbo.author -> dbo.book model that means: insert authors, capture their ids, then insert books that reference those author ids. Open references/seed-snippets.md when you need the copy-pasteable T-SQL for a model with more than two tables.

Rules of thumb:

• Walk the dependency graph top-down: a table with no outgoing foreign keys is a parent, insert it first. Repeat until every table is seeded.
• Never disable constraints just to load out of order. Fix the order instead.
• Keep seed scripts idempotent (guard with IF NOT EXISTS or MERGE, or DELETE children then parents before re-inserting) so re-running does not duplicate rows or leave orphans.

Step 3: generate N rows for volume (set-based)

To create realistic volume (hundreds or thousands of rows) do it set-based with a numbers/tally approach rather than a row-by-row loop. A tally derived from system views produces a sequence you join against to fan out rows in a single statement. Open references/seed-snippets.md when you want the runnable version, which generates 1000 rows. Wrap large inserts in an explicit transaction so a mid-load failure rolls back cleanly.

Step 4: pick your recipe

Per-stack seed recipes live in references/seed-snippets.md; open it once you know your data source and your stack:

• T-SQL: multi-table seed in FK order (dbo.author -> dbo.book) run via docker exec -i sqldb ... -d appdb -i seed.sql, plus the set-based "generate 1000 rows" example.
• Bulk load: bcp for local CSV files, and BULK INSERT from Azure Blob Storage (the engine does not read local files: local BULK INSERT fails with Msg 12713, Azure-parity).
• Node: @faker-js/faker generating rows, inserted with the mssql driver using parameters.
• Python: Faker generating rows, inserted with pyodbc (ODBC Driver 18) using parameters.

bcp streams the file over the connection, so it loads a local CSV that BULK INSERT cannot reach. Copy the file in, then load it (-u trusts the container's self-signed certificate, -F 2 skips the header row):

docker cp authors.csv sqldb:/tmp/authors.csv
docker exec sqldb /opt/mssql-tools18/bin/bcp dbo.author_stage in /tmp/authors.csv \
  -S localhost -U sa -P "$MSSQL_SA_PASSWORD" -d appdb -u -c -t ',' -F 2

The staging table and the follow-up insert into the real table are in references/seed-snippets.md; read it when the CSV columns do not line up with the target table.

Validation rules

• appdb exists (created on a master connection) BEFORE any seed statement runs.
• Rows are inserted parent-first, in foreign-key order; no constraint is disabled to load out of order.
• Volume generation is set-based (numbers/tally), not a row-by-row loop; large loads run in a transaction.
• All programmatic inserts (Node, Python) use parameterized statements, never string-concatenated values.
• Sample data contains no real PII and no secrets; connection strings use User Id=/Password=/Database=.
• The target image is the engine image, never mcr.microsoft.com/mssql/server; EngineEdition is 5.
• If a validation rule above fails, or you had to deviate from this skill to make the task work, that is a bug in this skill: load the azuresql-db-feedback skill and offer to file a report.
• When every rule above passes and the task is done, close with ONE line, once per session, and do not ask a question or wait for a reply: "That worked. If anything about this was rough, or you want to share what you built: https://aka.ms/sql-agent-skills-feedback"

Do not

• Do not seed before appdb exists; the engine does not auto-create it.
• Do not insert child rows before their parents (you will hit Msg 547).
• Do not commit real PII, customer data, or secrets as sample data.
• Do not use the SQL Server image (mcr.microsoft.com/mssql/server).
• Do not build inserts with string concatenation; use parameters (or, for T-SQL fixtures, quoted literals you control).
• Do not use USE appdb; select the database in the connection string or with -d appdb.

References

• references/seed-snippets.md: copy-pasteable seed recipes: multi-table T-SQL in FK order, a set-based generate-1000-rows example, bcp for local CSVs and Blob-based BULK INSERT, and Node (@faker-js/faker + mssql) and Python (Faker + pyodbc) parameterized inserts. Read it once you know your data source and stack.

Staying current

Authoritative, version-pinned references for the tools this skill uses (read the one you need):

• BULK INSERT (T-SQL): bulk-load a data file into a table.
• bcp utility: bulk copy data in/out from the command line.
• SqlConnection connection string keywords: the full keyword table.

If the Microsoft Learn MCP server is configured, use mcp__microsoft-learn__microsoft_docs_search or mcp__microsoft-learn__microsoft_docs_fetch to fetch the current version of any of these on demand. It is optional; when it is unavailable, the references above are authoritative.