CSV to SQL INSERT Conversion Prompt Template

This prompt is built for ETL engineers and data platform developers who need to convert raw CSV data into syntactically correct SQL INSERT statements. The ideal workflow involves a known target table schema and CSV files that require dialect detection, header-to-column mapping, type inference, quoting, and escape rule handling. Use this when you are automating data ingestion from spreadsheets, building AI-assisted database loading pipelines, or migrating flat-file data into a relational store and need insert-ready SQL that preserves row count and column alignment.

The prompt expects you to provide the raw CSV content and the target table schema. It will handle dialect detection (delimiter, quote character, escape rules), map CSV headers to database columns, infer data types, and apply correct quoting and escaping for the target SQL dialect. However, do not use this prompt when the target schema is unknown, when multi-table transactions with foreign key propagation are required, or when the CSV contains nested structures that must be flattened before insertion. For those scenarios, consider the Multi-Table Transaction Record Generation or JSON Document to Relational Row Mapping playbooks instead.

Before wiring this into a production pipeline, ensure you have a validation step that compares the generated INSERT row count against the source CSV row count and verifies column alignment. For high-stakes data migration, always include a human review stage or a dry-run execution against a staging database. If your CSV contains sensitive or regulated data, apply PII redaction before passing it to the model and log all generated SQL for audit trails.

This prompt template is designed to be copied directly into your AI harness, test suite, or orchestration layer. It instructs the model to act as a precise SQL generator that consumes CSV data and outputs only valid INSERT statements. The template uses square-bracket placeholders—such as [TARGET_TABLE], [COLUMN_SCHEMA], and [CSV_INPUT]—that you must replace with your specific table schema, dialect rules, and source data before execution. The prompt enforces strict output formatting: no markdown fences, no explanatory text, and one complete INSERT statement per CSV row.

textCopy
You are a precise SQL generator. Your only job is to convert the provided CSV data into valid SQL INSERT statements.

## Inputs
- Target table: [TARGET_TABLE]
- Column schema (name, type, nullable, default): [COLUMN_SCHEMA]
- CSV dialect (delimiter, quote character, escape character, header row): [CSV_DIALECT]
- CSV data: [CSV_INPUT]
- SQL dialect (e.g., PostgreSQL, MySQL, SQLite): [SQL_DIALECT]
- Null representation in CSV: [NULL_REPRESENTATION]
- Date/timestamp format in CSV: [DATE_FORMAT]

## Rules
1. Parse the CSV using the provided dialect. If a header row is present, map columns by name to the schema. If no header, map by ordinal position.
2. For each CSV row, generate exactly one INSERT statement targeting [TARGET_TABLE].
3. Apply type coercion: cast strings to the declared column type using explicit CAST or :: syntax appropriate for [SQL_DIALECT].
4. Handle NULLs: treat [NULL_REPRESENTATION] values as SQL NULL. For missing fields, use NULL unless a column default is specified in [COLUMN_SCHEMA].
5. Escape single quotes by doubling them. Escape backslashes according to [SQL_DIALECT] rules.
6. Quote all string, date, and timestamp values. Do not quote numeric or boolean values.
7. For date/timestamp columns, parse values using [DATE_FORMAT] and reformat to the standard literal format for [SQL_DIALECT].
8. If a value cannot be safely coerced to the declared type, emit a SQL comment on the preceding line: -- WARNING: Type coercion failed for column [name], raw value preserved.
9. Output ONLY the INSERT statements and any warning comments. No markdown fences, no explanations, no SELECT statements, no transaction wrappers.
10. Preserve row order from the CSV input.

## Output Format
-- WARNING: Type coercion failed for column [name], raw value preserved. (only when applicable)
INSERT INTO [TARGET_TABLE] ([comma, separated, column, names]) VALUES (correctly, typed, and, escaped, values);

After copying the template, replace each square-bracket placeholder with concrete values for your pipeline. For [COLUMN_SCHEMA], provide a structured description such as id INTEGER NOT NULL, name VARCHAR(255) NOT NULL DEFAULT 'unknown', created_at TIMESTAMP WITH TIME ZONE NULL. For [CSV_DIALECT], specify the exact delimiter, quote character, and whether a header row exists. The [NULL_REPRESENTATION] placeholder should capture how your source CSV encodes missing values—common values are \N, NULL, or an empty field. If your pipeline processes CSVs from multiple sources with varying dialects, consider making [CSV_DIALECT] a runtime parameter rather than hardcoding it. Before deploying this prompt to production, run it against a golden dataset where you know the exact expected INSERT statements, and diff the output to catch quoting, escaping, or type coercion errors early.

This prompt is designed to be the core transformation step inside a larger ETL pipeline, not a standalone chat interaction. The application layer is responsible for CSV parsing, dialect detection, and header extraction before the prompt ever sees the data. The model receives a pre-parsed representation of the CSV (headers plus a sample of rows) and the target table schema, and returns only the SQL INSERT statements. The application then executes those statements inside a transaction with rollback on failure.

Pipeline wiring: The recommended flow is: (1) Parse the CSV file using a library like csv-parser or Python's csv.DictReader to extract headers and detect the delimiter, quote character, and escape rules. (2) Read a configurable sample of rows (default 20, max 50 to stay within context limits) to include in the prompt's [CSV_SAMPLE] placeholder. (3) Load the target table schema from your database's information_schema or a schema registry and format it as a compact column definition block for the [TABLE_SCHEMA] placeholder. (4) Assemble the prompt with the template, send it to the model, and receive the INSERT statements. (5) Validate the output before execution: parse the SQL to count the number of INSERT statements and verify it matches the input row count, check that all column names in the INSERT match the target schema, and run a dry-run EXPLAIN or a transaction that rolls back to catch syntax errors. (6) On validation failure, retry once with the error message appended to the prompt as additional [CONSTRAINTS]. If the retry also fails, route to a human review queue with the original CSV sample, the failed SQL, and the validator error log.

Model choice and tool use: This task benefits from models with strong SQL generation and structured output capabilities. GPT-4o, Claude 3.5 Sonnet, and comparable models handle type inference and quoting well. For high-throughput pipelines, consider using a cheaper model for simple schemas and escalating to a stronger model only when validation fails. Do not give the model a SQL execution tool—the application must retain control over database writes. The model's job ends at generating the INSERT text. Logging: Log every generation attempt with the input row count, output row count, validation pass/fail, model used, latency, and any error messages. This trace data is essential for debugging type coercion failures and dialect mismatches that only appear at scale.

Human review gates: Enable mandatory human approval when: (a) the CSV contains columns that don't map to any target schema column, (b) the model infers types that conflict with the schema (e.g., treating a VARCHAR column as INTEGER), (c) the row count mismatch exceeds 1%, or (d) the pipeline is writing to a production database with no staging environment. The approval UI should show a side-by-side diff of the first few generated INSERTs against the original CSV rows so reviewers can spot quoting errors, NULL misinterpretation, or encoding issues quickly. Never auto-execute generated SQL against production without these gates in place.