OK,  so I was trying to kick this Code Generation bent I have been on of late, but… opportunity knocked yesterday for a query where one of the possible solutions involved codegen and I was weak  

So imagine we have some convieniently breif and neatly anonymized canonical example like the following table:

SELECT customer_id, order_id

FROM OrderCustomer

Now imagine the project is to remove all the rows from this table, except the lowest number order for  each customer.  The first step is to write a query to exceptionalize these rows:

SELECT customer_id, min(order_id) AS AS LowestOrderID

FROM OrderCustomer

GROUP BY customer_id

The next step is to select some string literals with the original query so that the result is valid T-SQL

SELECT 'DELETE FROM OrderCustomer WHERE customer_id = ', customer_id,

       'AND order_id > ', min(order_id) AS LowestOrderID

FROM OrderCustomer

GROUP BY customer_id

Tangentally the little unit of joy in this whole experience for me is that  the <Ctrl-T> keyboard shortcut is the same in SQL Server 2005 Management Studio as it was back in Query Analyzer. 

So, hit <Ctrl-T> to output the results window as Text, then F5 to return the results to get something like:

DELETE FROM OrderCustomer WHERE customer_id =  1           AND order_id >  32

DELETE FROM OrderCustomer WHERE customer_id =  2           AND order_id >  821

DELETE FROM OrderCustomer WHERE customer_id =  3           AND order_id >  1

(2 row(s) affected)

(5 row(s) affected)

(4 row(s) affected)

Select all the DELETE FROM statements and copy and paste them into a new query window, then F5 to remove the rows.

Look at the table to test the result:

SELECT customer_id, order_id

FROM OrderCustomer