How to Copy a Resultset Column as an IN Statement

|

In the realm of software engineering, we often work with data (stored in databases). On my team this is especially true. We are primarily a Microsoft shop (SQL Server, .NET, Visual Studio).

When performing data operations, it is often the case that you must find a batch of data, verify that’s the data that you need to update, and then update only those records that you have verified. This tip may help streamline these efforts.

There is a feature of Microsoft SQL Server Management Studio that allows you to “Copy as IN clause”!

This means after you’ve built a query and identified the records you need (most likely including an ID field), you can just copy the IDs you need preformatted as an IN clause. Noice!

How-To Steps

  • Run a query in SQL Server Management Studio
  • Select/highlight a list of GUID values (UNIQUEIDENTIFIER) in a single column of the result set.
  • Right-click
  • Copy as IN clause
  • Paste (CTRL+V) into your new query
  • Done!

Screenshots or it Didn’t Happen!

Screenshot of SQL Server Management Studio featuring the Right-Click Copy as IN clause functionality.
Right-click your selected ID column and click “Copy as IN clause”
Screenshot of SQL Server Management Studio featuring a SELECT statement with an IN clause.
Now you can just paste (CTRL+V) your new IN clause into a new query.

A Simple SQL Script

Here’s the test SQL script that I used in the above screenshots.

SELECT NEWID()
FROM master..spt_values
WHERE name IS NULL
AND number < 10


SELECT *
FROM [dbo].[Sample]
WHERE [ID] IN
(
    '{857a163f-dd75-4992-98e8-0d4e59de65cf}',
    '{89d8e5c7-631b-4018-81af-76ff6dd9f7d0}',
    '{3c134c71-9f07-4073-aff0-b7a4652b5ee6}',
    '{1e7ff335-80fc-443c-a768-c1cad95a52b9}',
    '{3f80fff9-1940-4e7a-ae6c-d84820ba2df7}',
    '{07ebc2db-f80f-4a2e-926e-cd6bba6eed8b}',
    '{3051cd3c-6ffd-4bad-9c3e-4c71acb2ed4c}',
    '{6fa56c78-d769-417e-b7ee-9a6009bea261}',
    '{8c271db6-6c90-4b40-a979-26a6057cd1bb}',
    '{6f6d386f-40f9-4796-ac5e-6cdbfcb58142}'
)

Bonus Tip

You may not have picked up the bonus tip above, so I’ll draw your attention to it. The first portion of the test SQL script above contains a handy way of generating a set number of values from a function, like the NEWID() function, which generates UNIQUEIDENTIFIER (Guid) values.

Cheers!