Skip to content

CLI Data

radix29 edited this page Oct 31, 2026 · 1 revision

← goSSMS Home · ← CLI

CLI — Data

The data group reads and profiles a table or view, streams every row of one to a file, and loads a file back into a table through bulk copy. Five reads and one write (data import); cells are the grid's text, JSON types them, and the output limits apply like anywhere else.

gossms data top    -p dev Sales.Customer                       # the first N rows
gossms data sample -p dev Sales.Customer --rows 20             # N random rows
gossms data count  -p dev Sales.SalesOrderDetail               # COUNT_BIG(*)
gossms data profile -p dev Sales.Customer --top-values 5       # nulls, ranges, top values
gossms data export -p dev Sales.Customer --out-file customers.csv
gossms data import -p dev Staging.Customer --in-file customers.csv --allow write

All the reads take a table or view as <object> (the name rules are Objects and scripting's); the login needs SELECT (Msg 229, or 230 for a denied column → exit 9). A procedure, function or synonym is exit 2 (a synonym's hint names its base object).

data top — look at the rows

gossms data top -p dev Sales.Customer
gossms data top -p dev Sales.SalesOrderHeader --rows 10 --order-by OrderDate -o json
gossms data top -p dev Sales.Customer --columns CustomerID,AccountNumber --where TerritoryID=4

The TUI's Select Top 1000 Rows: SELECT TOP (n) — --rows default 100, 0 returns the columns alone. The record carries object, type and tsql, the verbatim SELECT that was sent — every example in this wiki was checked against exactly that field's contract.

  • --columns a,[b c] lists column names (bracketed where needed). Each is matched against the object's columns — exactly, else ignoring case when that finds exactly one — and sent bracketed in the catalog's spelling, never as written. An unknown column is exit 8 naming columns that contain it; a column named twice, or anything but names and commas, is exit 2.
  • --where <expr> is a condition without the WHERE; --order-by a list without the ORDER BY (CreatedAt DESC, id). Without --order-by the order is whatever the server returns.
  • --rows above --max-rows needs --max-rows 0 or --out-file to show every row (the record still says what ran).

data sample — random rows, exactly

gossms data sample -p dev Sales.Customer --rows 20
gossms data sample -p dev Sales.SalesOrderHeader --where Status=5 -o json

data top with ORDER BY NEWID(): a uniformly random sample, different on every call. It is exact, not TABLESAMPLE's page sample — it reads and sorts all the rows --where keeps (a full scan; TABLESAMPLE returns nothing on a small table and refuses a view). data top is the cheap look; this is the representative one.

data count — exact, or free

gossms data count -p dev Sales.SalesOrderDetail
gossms data count -p dev Sales.SalesOrderDetail --approximate
gossms data count -p dev Sales.Customer --where TerritoryID=4 -o json

SELECT COUNT_BIG(*), with --where when given: exact, and it reads the table (or the view's query). --approximate reads the table's partition statistics instead — no scan, no session, as Object Explorer's row count does — and therefore refuses --where and a view (exit 2). The record: rows (NULL when the count failed), method (exact/approximate), tsql.

data profile — the columns in one answer

gossms data profile -p dev Sales.Customer
gossms data profile -p dev Sales.SalesOrderHeader --columns Status,OrderDate --top-values 5 -o json

One aggregate SELECT per up-to-100 columns (tsql shows them, GO between), answered as set columns:

Column
column, type The catalog's spelling and the base type (an alias type's)
rows The rows --where keeps
nulls, null_pct NULL count and share (two decimals; NULL pct with no rows)
distinct, min, max Numbers, dates/times, strings, binary, uniqueidentifier, sql_variant, hierarchyid, rowversion (bit as 0/1, since MIN(bit) is refused)
min_len, max_len Strings in characters (LEN — trailing spaces not counted), binary in bytes
— xml, text, ntext, image, geography, geometry, vector, CLR types and Always Encrypted columns get rows and nulls alone; what a column does not get is NULL
  • --top-values n adds set top_values (column, value, count): each column's n most frequent values — NULL counts as one — by count, one GROUP BY per column. The ANSI "Null value is eliminated by an aggregate" warning is dropped: counting NULLs is the point.
  • COUNT(DISTINCT) sorts or hashes every column: on a large table narrow with --columns or --where. A graph table's internal columns are left out and cannot be named in --columns.

data export — every row, to a file

gossms data export -p dev Sales.Customer --out-file customers.csv
gossms data export -p dev Sales.SalesOrderHeader --where Status=5 \
  --order-by SalesOrderID --out-file orders.jsonl
gossms data export -p dev --file report.sql --out-file report.tsv --force
gossms data export -p dev Sales.Customer --where "ModifiedDate >= '2026-01-01'" --out-file new.csv

Writes every row — no row, width or byte limit — to --out-file, which data export requires (exit 2 without, before connecting). The extension is the format, as for any --out-file: .csv/.tsv are the bytes the TUI's Results To File writes; .jsonl keeps types and tells NULL from empty string. The file is replaced only when the export succeeds (--force to replace an existing one; a failed export leaves it as it was).

  • Object form: SELECT * of it with data top's --columns, --where, --order-by and no TOP.
  • T-SQL form: --sql/--file/--sql-base64 (with --param as query run's), run held at class read: a script above read is BLOCKED (exit 7) before connecting, the hint pointing at query run --allow; mode options are refused; the run stops at the first failing batch. Every result set goes to the file.
  • An object together with T-SQL, or --columns/--where/--order-by without an object, is exit 2. The record answers object/type/tsql or detectedClass/statementCount, plus each set's totalRowCount and outFile.

data import — bulk copy in

gossms data import -p dev Sales.Customer --in-file customers.csv --allow write --dry-run
gossms data import -p dev Sales.Customer --in-file customers.jsonl --allow write
gossms data import -p dev Sales.Customer --in-file - --in-format csv --allow write < customers.csv
gossms data import -p dev Sales.Customer --in-file customers.csv \
  --truncate-first --confirm Sales.Customer --allow ddl

Loads --in-file into a table through gosmo's bulk copy — the TDS path bcp uses. Class write (--allow write, gated before connecting); --truncate-first makes it ddl. The server's refusals map the same way as everywhere: a missing INSERT — and a missing ALTER where identity values or unchecked constraints need it (Msg 1088, Msg 5304) — is exit 9.

  • The file is what data export writes, one result set: .csv/.tsv with a header row, read with the writer's NULL convention (an empty field NULL, "" the empty string, a bare NULL the string; a leading BOM is dropped), or gossms's .jsonl (the _resultSet line names the columns and their keys; numbers as written; _summary ends the rows). .json is refused: export .jsonl. --in-format csv|tsv|jsonl names the format for stdin (--in-file -, not with --password-stdin) or another extension. A blank line followed by more rows (a second set), a wrong field count, or a malformed field is exit 2 naming the line.
  • Columns are matched by name, exactly, else the one match ignoring case; a name the table lacks, or two naming one column, is exit 2. The table's columns the file lacks take their defaults; computed, rowversion and GENERATED ALWAYS columns in the file are skipped with one warning, so an export of the table loads back into it; an identity column in the file keeps the file's values. Set columns lists each file column with action load / skipped (…), then the defaulted ones.
  • Conversion is checked before anything is sent: range, precision and scale (more fractional digits than the column keeps is an error, not rounding), length in the column's unit, char text encodable to the database's code page. The first cell that fails is exit 2, line n, column [c]: …. sql_variant, CLR types, vector and Always Encrypted columns are UNSUPPORTED (exit 11) before loading. A one-column CSV writes a NULL as "", so it loads every "" as an empty string, with a warning; .jsonl keeps them apart.
  • The load is one batch, all or nothing: a bad cell, a constraint the rows break, or a cancel leaves the table as it was (rows 0). NULLs stay NULL (KEEPNULLS — a column's default does not replace them). CHECK and FOREIGN KEY constraints are checked unless --no-check-constraints (bcp's default, which leaves them untrusted — and needs ALTER); INSERT triggers fire only with --fire-triggers; --tablock takes a bulk-update lock. --timeout (default 60) bounds the load.
  • --truncate-first runs TRUNCATE TABLE before the load: needs --allow ddl, ALTER on the table, and --confirm with the table exactly as given (else exit 7 before connecting). A TRUNCATE cannot share the load's transaction: a load that fails afterwards leaves the table empty — the hint says so.
  • --dry-run reads and converts the whole file (a bad cell fails the dry run exactly as it would the load), counts rows, and loads nothing. A bulk load itself has no T-SQL to show; with --truncate-first, tsql is the TRUNCATE.

Fragments — how --where and --order-by are guarded

--where is spliced as WHERE (<expr> + line break + ), --order-by as ORDER BY <list> + line break, so a trailing -- comment ends at its line. A fragment that is blank or leaves a string, quoted name or block comment open is exit 2. The composed SELECT must classify as one read SELECT in one batch, no ; outside a literal or comment and no flag (dynamic SQL, a procedure call), else BLOCKED, exit 7, before connecting — the record names the statements found. The check keeps a fragment from adding a statement; what the condition reads is the login's permissions' business.

Recipes

# round-trip a table between two servers (types and NULLs preserved via .jsonl)
gossms data export  -p prod Sales.Customer --out-file customers.jsonl
gossms data import  -p stg  Sales.Customer --in-file customers.jsonl --allow write --dry-run
gossms data import  -p stg  Sales.Customer --in-file customers.jsonl --allow write

# size a table before adding a NOT NULL constraint
gossms data count  -p prod Sales.SalesOrderDetail --approximate
gossms data profile -p prod Sales.SalesOrderDetail \
  --columns SalesPersonID,OrderQty --top-values 3 -o json |
  jq '.resultSets[] | select(.name=="columns") | .rows[] | {column, nulls, distinct}'

# a quick CSV for the analytics team, refreshed by cron
echo '0 2 * * * gossms data export -p prod Sales.vw_OrdersDaily --out-file /srv/share/orders.csv --force' | crontab -

Clone this wiki locally