Skip to main content

How to Seed a Database from a Batch Script

Database seeding is the process of populating a database with initial or sample data. This includes reference data (countries, currencies, roles), default configuration values, test datasets for development environments, and demo data for product demonstrations. Automating seeding through Batch Script ensures that every environment (development, staging, QA) starts with a consistent, predictable dataset.

In this guide, we will explore how to seed databases from a Batch Script using SQL scripts, CSV imports, and application-level seeders.

Method 1: Seeding with SQL Scripts​

The most direct approach uses SQL INSERT statements:

@echo off
setlocal enabledelayedexpansion

set "server=localhost"
set "database=MyAppDB"
set "seed_dir=database\seeds"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found. Install SQL Server command-line tools.
    pause
    exit /b 1
)

if not exist "%seed_dir%\" (
    echo [ERROR] Seed directory not found: %seed_dir%
    pause
    exit /b 1
)

echo =============================================
echo   DATABASE SEEDER
echo   Server:   %server%
echo   Database: %database%
echo =============================================
echo.

:: Run seed scripts in order
set "count=0"
set "failed=0"

for /f "delims=" %%F in ('dir /b /on "%seed_dir%\*.sql" 2^>nul') do (
    set /a count+=1
    echo Seeding: %%F
    sqlcmd -S "%server%" -d "%database%" -i "%seed_dir%\%%F" -b >nul 2>&1

    if !errorlevel!==0 (
        echo       [OK]
    ) else (
        echo       [FAIL]
        set /a failed+=1
    )
)

if !count!==0 (
    echo No .sql files found in %seed_dir%.
    pause
    exit /b 1
)

echo.
if !failed! gtr 0 (
    echo [WARNING] !failed! of !count! seed scripts failed.
    exit /b 1
) else (
    echo [DONE] All !count! seed scripts applied.
)

pause

Example Seed Script (001_roles.sql)​

-- Seed default roles (idempotent)
IF NOT EXISTS (SELECT 1 FROM Roles WHERE Name = 'Admin')
    INSERT INTO Roles (Name, Description) VALUES ('Admin', 'Full system access');

IF NOT EXISTS (SELECT 1 FROM Roles WHERE Name = 'User')
    INSERT INTO Roles (Name, Description) VALUES ('User', 'Standard user access');

IF NOT EXISTS (SELECT 1 FROM Roles WHERE Name = 'ReadOnly')
    INSERT INTO Roles (Name, Description) VALUES ('ReadOnly', 'View-only access');
tip

Always write idempotent seed scripts using IF NOT EXISTS checks or MERGE statements. This allows the seeder to run repeatedly without creating duplicate data.

Method 2: Environment-Specific Seeding​

Different environments need different data:

@echo off
setlocal enabledelayedexpansion

set "server=localhost"
set "database=MyAppDB"
set "seed_dir=database\seeds"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found.
    pause
    exit /b 1
)

:: Determine environment
if "%~1"=="" (
    echo Usage: seed.bat [dev^|staging^|production]
    echo.
    set /p "env=Select environment: "
) else (
    set "env=%~1"
)

if not defined env (
    echo [ERROR] No environment specified.
    pause
    exit /b 1
)

echo =============================================
echo   SEEDING: !env!
echo =============================================
echo.

set "total=0"
set "failed=0"

:: Common seeds (always run)
if exist "%seed_dir%\common\" (
    echo Running common seeds...
    for /f "delims=" %%F in ('dir /b /on "%seed_dir%\common\*.sql" 2^>nul') do (
        set /a total+=1
        echo       %%F
        sqlcmd -S "%server%" -d "%database%" -i "%seed_dir%\common\%%F" -b >nul 2>&1
        if !errorlevel! neq 0 (
            echo       [FAIL]
            set /a failed+=1
        )
    )
) else (
    echo No common seed directory found. Skipping.
)

:: Environment-specific seeds
echo.
echo Running !env! seeds...
if exist "%seed_dir%\!env!\" (
    for /f "delims=" %%F in ('dir /b /on "%seed_dir%\!env!\*.sql" 2^>nul') do (
        set /a total+=1
        echo       %%F
        sqlcmd -S "%server%" -d "%database%" -i "%seed_dir%\!env!\%%F" -b >nul 2>&1
        if !errorlevel! neq 0 (
            echo       [FAIL]
            set /a failed+=1
        )
    )
) else (
    echo       No !env!-specific seeds found.
)

echo.
if !total!==0 (
    echo [WARNING] No seed scripts were found.
) else if !failed! gtr 0 (
    echo [WARNING] !failed! of !total! seed scripts failed.
    exit /b 1
) else (
    echo [DONE] All !total! seed scripts applied.
)

pause

Directory Structure​

database/seeds/
  common/
    001_roles.sql
    002_permissions.sql
    003_countries.sql
  dev/
    001_test_users.sql
    002_sample_orders.sql
  staging/
    001_demo_users.sql
  production/
    001_admin_user.sql

Method 3: CSV Data Import​

Import data from CSV files using BULK INSERT:

@echo off
setlocal enabledelayedexpansion

set "server=localhost"
set "database=MyAppDB"
set "data_dir=database\seed-data"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found.
    pause
    exit /b 1
)

if not exist "%data_dir%\" (
    echo [ERROR] Data directory not found: %data_dir%
    pause
    exit /b 1
)

echo =============================================
echo   CSV DATA IMPORT
echo =============================================
echo.

:: Resolve to absolute path (BULK INSERT requires it)
pushd "%data_dir%"
set "abs_data_dir=%CD%"
popd

set "count=0"
set "failed=0"

:: Import each CSV file (filename matches table name)
for %%F in ("%data_dir%\*.csv") do (
    set /a count+=1
    set "table=%%~nF"
    echo Importing %%~nxF into [!table!]...

    sqlcmd -S "%server%" -d "%database%" -Q ^
        "BULK INSERT [!table!] FROM '!abs_data_dir!\%%~nxF' WITH (FORMAT = 'CSV', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')" -b >nul 2>&1

    if !errorlevel!==0 (
        echo       [OK]
    ) else (
        echo       [FAIL]
        set /a failed+=1
    )
)

if !count!==0 (
    echo No .csv files found in %data_dir%.
    pause
    exit /b 1
)

echo.
if !failed! gtr 0 (
    echo [WARNING] !failed! of !count! imports failed.
    exit /b 1
) else (
    echo [DONE] All !count! CSV files imported.
)

pause

Method 4: Full Reset and Seed​

For development environments, clear all data and reseed from scratch:

@echo off
setlocal enabledelayedexpansion

set "server=localhost"
set "database=MyAppDB_Dev"
set "seed_dir=database\seeds\dev"

:: Verify sqlcmd is available
where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found.
    pause
    exit /b 1
)

if not exist "%seed_dir%\" (
    echo [ERROR] Seed directory not found: %seed_dir%
    pause
    exit /b 1
)

echo =============================================
echo   RESET AND SEED (DEV ONLY^)
echo =============================================
echo.

echo [WARNING] This will DELETE all data in %database%.
set /p "confirm=Type RESET to continue: "
if /i not "!confirm!"=="RESET" (
    echo [CANCELLED]
    pause
    exit /b 0
)

:: Verify database connectivity before destructive operations
sqlcmd -S "%server%" -d "%database%" -Q "SELECT 1" -b >nul 2>&1
if !errorlevel! neq 0 (
    echo [ERROR] Cannot connect to %server%\%database%.
    pause
    exit /b 1
)

:: Step 1: Disable constraints and delete all data
echo.
echo [1/3] Clearing all tables...
sqlcmd -S "%server%" -d "%database%" -b -Q ^
    "EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'; EXEC sp_MSforeachtable 'DELETE FROM ?'; EXEC sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'"

if !errorlevel! neq 0 (
    echo [ERROR] Table cleanup failed.
    pause
    exit /b 1
)
echo       Done.

:: Step 2: Reset identity columns
echo [2/3] Resetting identity columns...
sqlcmd -S "%server%" -d "%database%" -b -Q ^
    "EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'', RESEED, 0)'" >nul 2>&1
echo       Done.

:: Step 3: Run seed scripts
echo [3/3] Seeding...
set "seed_failed=0"
for /f "delims=" %%F in ('dir /b /on "%seed_dir%\*.sql" 2^>nul') do (
    echo       %%F
    sqlcmd -S "%server%" -d "%database%" -i "%seed_dir%\%%F" -b >nul 2>&1
    if !errorlevel! neq 0 (
        echo       [FAIL]
        set /a seed_failed+=1
    )
)

echo.
if !seed_failed! gtr 0 (
    echo [WARNING] !seed_failed! seed scripts failed.
) else (
    echo [DONE] Database reset and seeded.
)

:: Show counts
echo.
echo Table row counts:
sqlcmd -S "%server%" -d "%database%" -Q ^
    "SET NOCOUNT ON; SELECT t.NAME AS TableName, p.rows AS RowCount FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.object_id WHERE p.index_id IN (0,1) AND p.rows > 0 ORDER BY p.rows DESC"

pause

Method 5: Application-Level Seeding​

Use application seeding commands for frameworks that provide them:

@echo off
setlocal enabledelayedexpansion

echo =============================================
echo   APPLICATION SEEDER
echo =============================================
echo.

set "failed=0"

:: .NET EF Core seeding (via custom command)
echo [.NET] Running EF Core seed...
dotnet run --project MyWebApp -- --seed
if !errorlevel! neq 0 (
    echo       [FAIL]
    set /a failed+=1
) else (
    echo       [OK]
)
echo.

:: Django (Python)
echo [Django] Running seed...
python manage.py loaddata fixtures/initial_data.json
if !errorlevel! neq 0 (
    echo       [FAIL]
    set /a failed+=1
) else (
    echo       [OK]
)
echo.

:: Laravel (PHP)
echo [Laravel] Running seed...
php artisan db:seed
if !errorlevel! neq 0 (
    echo       [FAIL]
    set /a failed+=1
) else (
    echo       [OK]
)
echo.

:: Rails (Ruby)
echo [Rails] Running seed...
call rake db:seed
if !errorlevel! neq 0 (
    echo       [FAIL]
    set /a failed+=1
) else (
    echo       [OK]
)

echo.
if !failed! gtr 0 (
    echo [WARNING] !failed! seeder(s^) failed.
) else (
    echo [DONE] All seeders completed.
)

pause

.NET EF Core Data Seeding​

@echo off
:: EF Core supports HasData() in OnModelCreating
:: Run a migration update to apply seeded data
dotnet ef database update --project MyWebApp

if %errorlevel%==0 (
    echo [OK] EF Core seed data applied via migrations.
) else (
    echo [ERROR] Migration failed.
)

pause

Generating Test Data​

Create large datasets for performance testing:

@echo off
setlocal

set "server=localhost"
set "database=MyAppDB_Dev"
set "row_count=10000"

where sqlcmd >nul 2>&1
if %errorlevel% neq 0 (
    echo [ERROR] sqlcmd not found.
    pause
    exit /b 1
)

echo Generating %row_count% test records...

sqlcmd -S "%server%" -d "%database%" -b -Q ^
    "SET NOCOUNT ON; DECLARE @i INT = 1; WHILE @i ^<= %row_count% BEGIN INSERT INTO Users (Username, Email, CreatedAt) VALUES ('user_' + CAST(@i AS VARCHAR), 'user' + CAST(@i AS VARCHAR) + '@test.com', DATEADD(DAY, -ABS(CHECKSUM(NEWID())) %% 365, GETDATE())); SET @i = @i + 1; END; PRINT CAST(@i - 1 AS VARCHAR) + ' records inserted.'"

if %errorlevel%==0 (
    echo [DONE] %row_count% records inserted.
) else (
    echo [ERROR] Data generation failed.
)

pause

Common Mistakes​

The Wrong Way: Non-Idempotent Seed Scripts​

-- WRONG - Running twice creates duplicate data
INSERT INTO Roles (Name) VALUES ('Admin');
INSERT INTO Roles (Name) VALUES ('User');
-- Second run: constraint violation or duplicate rows

Output Concern: Seed scripts that unconditionally insert data fail or create duplicates when run more than once. Always use IF NOT EXISTS, MERGE, or INSERT ... WHERE NOT EXISTS patterns for idempotent seeding.

The Wrong Way: Seeding Production with Test Data​

:: WRONG - Running dev seeds in production
sqlcmd -S production-server -d ProdDB -i "seeds\dev\test_users.sql"
:: Inserts fake users into the production database

Keep environment-specific seed data in separate directories and verify the target environment before executing.

Best Practices​

  1. Write idempotent scripts: Use IF NOT EXISTS or MERGE so seeds can run repeatedly.
  2. Separate common from environment seeds: Reference data goes in common; test data goes in dev only.
  3. Order scripts with numeric prefixes: Ensure foreign key dependencies are satisfied.
  4. Never seed test data in production: Separate seed directories by environment and verify before running.
  5. Verify after seeding: Query row counts to confirm data was inserted correctly.

Conclusion​

Seeding a database from a Batch Script automates the population of initial and reference data across all environments. Whether using SQL scripts with sqlcmd, CSV imports with BULK INSERT, or application-level seeders from .NET, Django, or Laravel, the core pattern is the same: run ordered, idempotent scripts that insert data only if it does not already exist. By separating seed data by environment, providing full reset-and-seed workflows for development, and verifying results with row counts, teams ensure that every environment starts with the correct, consistent dataset.

Table of Contents