SSMS adds comma delimiter - shortcut

Question

Is there a shortcut to add commas for values ​​for the IN clause? for example if my query is SELECT * FROM Persons WHERE Id IN(.....) and I copied

 356 9874 5975 9771 4166 .... 

The values ​​of the user ID allow us to say from some Excel file how I can quickly add a comma to the end of each line so that I can insert it into the IN (....) clause?

Answer 1

Here is what you need to do:

• Place the cursor one place after 356 (you will see that you will need additional space in step 2).
• Hold ALT + SHIFT and press the DOWN key to make as many numbers as you have. You should see a blue line to the right of your numbers. That's why you needed extra space after 356 - so you can just shoot the entire list without moving left or right.

3. Release the keys and press the comma key. A comma should appear on all lines.

You can use this method to add quotes or other characters to your SQL queries.

Answer 2

I use this in SSMS 2014;

I'm not sure if this can be done in previous versions

Answer 3

Yes, it is always a pain .. There are a few things you can do:

• paste the commas into the cells to the right of the numbers in Excel and copy them with the list into SSMS.
• in Notepad ++ , copy the list with the values ​​and press CTRL + H (find and replace) in search mode, click Advanced . In the Find field, enter "\ n" (without quotation marks) and in the Replace with comma ""

Hope this helps!

Answer 4

With SSMS 2012, you can draw a vertical line at the end of the code with the mouse by pressing the ALT key. After that, just press the comma key and on it.

Answer 5

Write a small program like the one below and run it from Launchy .

I wrote mine in C # - called it Commander ... probably the best name and the best program ever written.

 using System; using System.Windows.Forms; namespace Commander { internal class Program { [STAThread] private static void Main() { var clipboardText = Clipboard.GetText(TextDataFormat.Text); Clipboard.SetText(clipboardText.Contains(",") ? clipboardText.Replace(",", Environment.NewLine) : clipboardText.Replace(Environment.NewLine, ",").TrimEnd(',')); } } } 

Compile the above and make a link to the resulting Commander.exe from Launchy.

After the link: 1- select the character column and cut or copy the text 2- Summon Launch (Alt-Enter if you use the default shortcut) 3- Type Commander 4- Paste 5- Enjoy your list with a comma

Enter Commander again from the launch list with a comma-separated list, and the operation will be canceled. Read the code ... it's kind of obvious :)