SQL Coding Examples

SQL Server Coding Example: Reusable Municipal Data Import Stored Procedure

Modern business systems often need to import data from external tables, clean inconsistent values, standardize names and addresses, and move the results into a production database.

This SQL Server example demonstrates how I build a reusable stored procedure to handle that process.

The procedure accepts a source table name, loads the source data into a staging table, cleans the information, and inserts standardized records into the main billing table.

What This Example Demonstrates

This stored procedure uses several SQL Server development techniques:

  • Stored procedure development
  • Dynamic SQL
  • Staging tables
  • Data cleansing
  • String manipulation
  • Name parsing
  • Address normalization
  • Account number standardization
  • INSERT / SELECT processing
  • Reusable database architecture

The Business Problem

Different municipalities or customers may provide billing information using the same general structure but store the data in different import tables.

Instead of creating a separate stored procedure for every customer, the procedure accepts the source table as a parameter.

For example:

EXEC dbo.MoveMuniData
    @FromTable = 'ImportedBillingData';

This allows the same import process to be reused with multiple data sources.

SQL Server Stored Procedure

ALTER PROCEDURE dbo.MoveMuniData
    @FromTable varchar(max)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @SQL nvarchar(max);

    ------------------------------------------------------------
    -- Clear existing production billing data
    ------------------------------------------------------------
    DELETE FROM dbo.BillData;


    ------------------------------------------------------------
    -- Remove previous staging table
    ------------------------------------------------------------
    IF OBJECT_ID(N'dbo.BillData_New', N'U') IS NOT NULL
        DROP TABLE dbo.BillData_New;


    ------------------------------------------------------------
    -- Create staging table
    ------------------------------------------------------------
    CREATE TABLE dbo.BillData_New
    (
        id INT IDENTITY(1,1) NOT NULL,
        AccountNumber VARCHAR(MAX) NULL,
        Name VARCHAR(MAX) NULL,
        FName VARCHAR(MAX) NULL,
        LName VARCHAR(MAX) NULL,
        Address VARCHAR(MAX) NULL,
        Amount01 DECIMAL(18,2) NULL,
        isPaid INT NULL,

        CONSTRAINT PK_BillData_New
            PRIMARY KEY CLUSTERED (id ASC)
    );


    ------------------------------------------------------------
    -- Import source data
    ------------------------------------------------------------
    SET @SQL = '
        INSERT INTO dbo.BillData_New
        (
            AccountNumber,
            Name,
            Address,
            Amount01,
            IsPaid
        )
        SELECT
            AccountNumber,
            Name,
            ServiceAddress,
            AccountBalance,
            0
        FROM ' + @FromTable + ';
    ';

    EXEC sp_executesql @SQL;


    ------------------------------------------------------------
    -- Parse first name
    ------------------------------------------------------------
    UPDATE dbo.BillData_New
    SET FName =
        UPPER(
            LTRIM(
                SUBSTRING(
                    Name,
                    1,
                    IIF(
                        CHARINDEX(',', Name) > 0,
                        CHARINDEX(',', Name),
                        CHARINDEX(' ', Name)
                    )
                )
            )
        );


    ------------------------------------------------------------
    -- Parse last name
    ------------------------------------------------------------
    UPDATE dbo.BillData_New
    SET LName =
        UPPER(
            LTRIM(
                SUBSTRING(
                    Name,
                    CHARINDEX(' ', Name) + 1,
                    LEN(Name) - CHARINDEX(' ', Name) + 1
                )
            )
        );


    ------------------------------------------------------------
    -- Standardize names and account numbers
    ------------------------------------------------------------
    UPDATE dbo.BillData_New
    SET
        LName = REPLACE(LName, ',', ' '),
        FName = REPLACE(FName, ',', ' '),
        AccountNumber = REPLACE(AccountNumber, '-', '');


    UPDATE dbo.BillData_New
    SET
        Name = REPLACE(Name, '&', ''),
        FName = REPLACE(FName, '&', ''),
        LName = REPLACE(LName, '&', '');


    ------------------------------------------------------------
    -- Clean address characters
    ------------------------------------------------------------
    UPDATE dbo.BillData_New
    SET Address =
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        Address,
                        CHAR(33), ' '
                    ),
                    CHAR(35), ' '
                ),
                CHAR(36), ' '
            ),
            CHAR(37), ' '
        );


    ------------------------------------------------------------
    -- Insert cleaned data into production
    ------------------------------------------------------------
    INSERT INTO dbo.BillData
    (
        AcctNum1,
        Id_Dept,
        FullName,
        LName,
        FName,
        SvcAddress,
        Address1,
        IsPaid,
        Type,
        Escrow,
        Retired,
        Contract,
        Foreclosure,
        CreateDateTime
    )
    SELECT
        AccountNumber,
        1,
        Name,
        LName,
        FName,
        Address,
        Address,
        0,
        0,
        0,
        0,
        0,
        0,
        GETDATE()
    FROM dbo.BillData_New;

END

How the Process Works

The overall data flow is:

Source Import Table
        ↓
MoveMuniData Stored Procedure
        ↓
BillData_New Staging Table
        ↓
Data Cleansing
        ↓
Name and Address Standardization
        ↓
Production BillData Table

The staging-table approach provides an intermediate location where incoming data can be inspected, transformed, and validated before it reaches the production table.

Dynamic SQL

The source table is supplied when the stored procedure is executed.

EXEC dbo.MoveMuniData
    @FromTable = 'MunicipalImport2026';

The procedure dynamically creates the import statement:

SET @SQL = '
    INSERT INTO dbo.BillData_New
    (
        AccountNumber,
        Name,
        Address,
        Amount01,
        IsPaid
    )
    SELECT
        AccountNumber,
        Name,
        ServiceAddress,
        AccountBalance,
        0
    FROM ' + @FromTable;

This eliminates the need to maintain nearly identical stored procedures for each import source.

Data Cleansing

Imported business data is rarely perfectly formatted.

Account numbers may contain characters that are not required by the production application.

For example:

123-456-789

can be normalized to:

123456789

using:

UPDATE dbo.BillData_New
SET AccountNumber =
    REPLACE(AccountNumber, '-', '');

The same process can be used to remove or replace unwanted characters in names and addresses.

Why Use a Staging Table?

A staging table provides an important separation between imported data and production data.

Instead of placing external data directly into the production billing table, the system first places it into:

BillData_New

The data can then be cleaned and normalized before being inserted into:

BillData

This pattern is commonly used in ETL processes, data migrations, accounting systems, payment-processing systems, and legacy application modernization projects.

Potential Production Improvements

A production version of this procedure could be extended with:

  • Transaction handling
  • TRY / CATCH error handling
  • Import logging
  • Record validation
  • Duplicate detection
  • Row-count auditing
  • Invalid-record tables
  • Import timestamps
  • User or batch tracking
  • Safer dynamic SQL using QUOTENAME()
  • Automatic rollback when an import fails

For example, the source table name can be protected using SQL Server’s QUOTENAME() function before building the dynamic statement.

DECLARE @SafeTable nvarchar(258);

SET @SafeTable = QUOTENAME(@FromTable);

Where This Type of Code Is Useful

This approach can be used for:

  • Municipal billing systems
  • Payment-processing systems
  • Utility billing
  • Property tax systems
  • Customer account conversions
  • FoxPro to SQL Server migrations
  • DBF database conversions
  • Legacy application modernization
  • Accounting data imports
  • ETL and data integration projects

SQL Server Development and Legacy Application Modernization

I have extensive experience working with SQL Server, C#, legacy database applications, data conversions, reporting systems, and business applications.

I help organizations maintain existing systems while providing a practical path toward modern SQL Server and .NET architectures.

Areas I work with include:

SQL Server Development

Stored procedures, database design, performance tuning, data conversions, ETL processes, reporting databases, and application integration.

Legacy FoxPro and Clipper Applications

Support, troubleshooting, DBF recovery, data conversion, and migration of legacy applications.

FoxPro to SQL Server Migration

Moving DBF-based applications toward centralized SQL Server databases while preserving critical business logic.

C# and ASP.NET Development

Modern .NET applications, REST APIs, database-driven applications, and integration services.

Automation and AI Workflows

n8n workflow automation, APIs, AI integration, document processing, and Retrieval-Augmented Generation systems.

Need Help With a SQL Server or Legacy Application?

If your organization has a SQL Server application, FoxPro system, Clipper application, DBF database, or legacy business system that needs support or modernization, I can help evaluate the existing system and determine the best path forward.

Contact Ken Roach to discuss SQL Server development, legacy software support, database migration, or application modernization.