When copying a list of items from something like Excel, it would be lovely to have a feature in Prompt which would highlight the list, hit a key stroke and it would place quotes around each item and a comma after them.
It would stop me bashing the quote + arrow + comma keys at the speed of light.
This is included in SQL Prompt 7 which you can download from http://www.red-gate.com/products/sql-development/sql-prompt/
yes you can use SSMS replace with regular expressions, or you can use Excel, or you can write a C# program, but none of those are fast or convenient. That's what SQL Prompt was made for. I suggest the Format SQL (CTRL K + CTRL Y) comma separate items in an "IN" clause and use the column's data type to determine whether to enclose in single quotes (datetime, character data) or not (numeric data).
Chris Luttrell commented
I use the Regular Expressions trick all the time in these scenarios, but it is hard to teach new developers about the nuances of the version in SQL Server. This would be much faster to use and easier to teach.
Hey, all of you--how about using the tools already at your disposal, the replace function in SSMS. Check the Use Regular Expressions checkbox.
-- Highlight the list.
-- Find: \n
-- Replace with ',\n'
Then, all you have to do is add the initial ' and remove the comma and apostrophe at the end of the list.
I currently do the same thing Kresten does...would love to see this added.
I agree It would be great, would save me from coding in excel :)
="'" & A1 & "',"
and copying it all the way down, then copying the result into ssms and deleting the last ,
Paul Nuttall commented
Have you tried search/replace using Regular Expressions?
This works great.
I paste the list into SSMS window
Select the list and run search replace for \n with '\n,'
there you have it
Jerry Pett commented
Yep, great idea, I suggested something similar previously. Would use it all the time!
Stuart Branham commented
I love bashing comma/arrow, especially on a good keyboard. Feels good man. Honestly, this would be a great feature, I just don't think I'd use it. ;)
Paul Fletcher commented
I would use this every day