Sharing Query Results From SSMS: Copy With Headers, Grids and CSV

Copy a result without column labels, and an address field can become difficult to interpret. Using copy with headers fixes the first problem. Deliberate export formatting fixes the rest.

Shape the Result Before Copy With Headers

I review the query before worrying about the export format. Explicit columns give the recipient a stable shape. SELECT * leaves that shape dependent on later schema changes.

Use clear aliases and a deterministic ORDER BY. A saved result doesn't inherit a reliable row order from a table. Add a unique tiebreaker when the first ordering column repeats.

Also agree on the filter and collection time. A file without its reporting boundary is difficult to compare. Share that context separately from the result when the recipient needs it.

The sample below includes a comma, quotes and a line break. Those are deliberate test inputs. They expose format problems that a plain numeric result hides.

Run it in an ordinary SSMS Database Engine window. It doesn't modify a table. Use the returned grid to test the copying options on your installed SSMS version.

SELECT RecordId, LabelText, NoteText
FROM (VALUES
 (1, N'North, Inc.', N'Said "approved"'),
 (2, N'South', N'First line' + NCHAR(13) + NCHAR(10) + N'Second line')
) AS v(RecordId, LabelText, NoteText)
ORDER BY RecordId;

Use Copy With Headers in the Grid

Select the required cells in the grid. Use the grid context menu's Copy with Headers command. The column names travel with the selected data.

The ordinary Copy command leaves the column names behind by default. The difference matters when the recipient doesn't know your schema. A column labeled Amount still deserves a definition of its units.

Grid copying is convenient for small results. It isn't a durable exchange specification. The destination application decides how it interprets pasted tabs and line breaks.

I paste the sample into a plain text editor first. That makes separators visible before another application interprets them. Check that the header count matches the selected columns.

Copy with headers helps identify a column, not its meaning. A UTC timestamp still needs a stated zone. An identifier with leading zeros still needs protection from numeric conversion.

Choose Text Output When Copy With Headers Falls Short

Ctrl+T selects Results to Text. Execute the query again after changing the destination. The existing grid doesn't turn into a newly formatted text result automatically.

Tools, Options, Query Results, SQL Server, Results to Text contains output choices. Select comma-delimited or tab-delimited output when that is your intended format. Check column width and header settings there too.

Text output can include formatting that isn't data. Header separator lines and padding deserve inspection. Don't hand a report-style text output to an automated loader without testing it.

The maximum character setting also matters for wide columns. A displayed value that looks complete isn't proof that nothing was truncated. Include a long test value before relying on the format.

A comma delimiter alone doesn't make a trustworthy CSV file. Fields containing commas, quotes or newlines require quoting rules. Renaming the extension doesn't supply those rules.

Save a File Without Using the Clipboard

Ctrl+Shift+F selects Results to File. Execute the query and choose the destination file. Inspect the selected format and encoding rather than accepting the default extension blindly.

File output avoids a clipboard step. It still needs validation of delimiters, headers and text widths. The destination option doesn't guarantee the recipient's parser understands the result.

The grid also supports Save Results As through its context menu. Available formats depend on your SSMS version. Test the installed behavior with the deliberately awkward sample values.

Keep the original query beside the delivered result when reproducibility matters. Record parameter values separately where permitted. A file alone cannot explain every selection decision.

What will open this file at the other end? A human viewer and an automated import need different guarantees. Choose the format for that destination.

Preserve Line Breaks Only When Required

The grid option Retain CR/LF on copy or save controls copied or saved grid values. Enable it when embedded line breaks are part of the data. Open a new query window after changing options if the current window retains earlier settings.

Preserved line breaks require a format that keeps them inside the field. In properly quoted CSV, a field can span physical lines. Counting physical lines then isn't the same as counting records.

Disabling retention changes how grid text is copied. It doesn't remove the original characters from the database. Decide whether that presentation change is acceptable to the recipient.

The option belongs to grid behavior. Don't assume it controls all text and file output paths. Run the same sample through the specific path you intend to use.

I check the embedded break after saving and reopening the result. A preview in the grid isn't the final exchange. The address field gets an undeserved promotion when it becomes two records.

Quote CSV Fields Explicitly

A robust CSV field doubles internal quotes and wraps the field in quotes. Quoting every text field is a straightforward convention. Nulls need an agreed representation distinct from empty text when that difference matters.

The next query constructs quoted sample lines explicitly. It removes no commas or quotes from the source. The returned string is the record representation you must preserve during export.

Don't let a later exporter quote that assembled line as another field. Also avoid adding a second set of delimiters around it. Validate the final file with its intended reader.

For a larger schema, define encoding, column order and null behavior as part of the export contract. An attractive grid doesn't define any of them. Test unusual characters and wide values too.

SELECT N'"' + CONVERT(nvarchar(10), RecordId) + N'","'
       + REPLACE(LabelText, N'"', N'""') + N'","'
       + REPLACE(NoteText, N'"', N'""') + N'"' AS CsvRecord
FROM (VALUES
 (1, N'North, Inc.', N'Said "approved"'),
 (2, N'South', N'First line' + NCHAR(13) + NCHAR(10) + N'Second line')
) AS v(RecordId, LabelText, NoteText)
ORDER BY RecordId;

Move Large Results Beyond the Grid

For large exports, use the SQL Server command-line utilities installed for your environment. The examples use Windows authentication and placeholder server and database names. Replace them with the authorized source.

The first command writes tab-delimited Unicode data with bcp. It doesn't include column headers or CSV quoting automatically. Tabs and line breaks inside data still need an exchange policy.

The second writes a simple comma-separated numeric report with sqlcmd. Its selected fields contain no embedded delimiters. The -h -1 option suppresses headers, so provide the schema separately.

REM Command line
bcp "SELECT object_id, name FROM SampleDatabase.sys.tables ORDER BY object_id" queryout "C:\SqlExports\tables.tsv" -S "SERVER\INSTANCE" -T -w
sqlcmd -S "SERVER\INSTANCE" -d "SampleDatabase" -E -Q "SET NOCOUNT ON; SELECT object_id, schema_id FROM sys.tables ORDER BY object_id;" -s "," -W -h -1 -o "C:\SqlExports\table-ids.csv"

I ran both commands against a test database. The bcp file held tab-separated rows with no header line, and the sqlcmd file held bare comma-separated numbers. With ODBC Driver 18 and a self-signed server certificate, add -u to bcp and -C to sqlcmd, or the connection is refused.

Create the output directory first and confirm read permissions for the source. Inspect command completion and the saved file. Don't publish sensitive result files into a shared location by accident.

Copy with headers is the right beginning for a small shared grid. Larger exchanges deserve a written format and a verified file. Check the first record, last record and awkward values before sending it.

Related reading on this blog: Troubleshooting Common CSV Import Issues and Configurable KeyBoard Query Shortcuts for SSMS.

A shared result is not merely a copied grid, it is data with a readable and tested format.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.