SQL Prompt Results grid actions (copy as IN clause, Script as INSERT, Open in Excel)

In this tip Data Platform MVP Cathrine Wilhelmsen explains how to use SQL Prompt to quickly work with data from the Results grid in SQL Server Management Studio or Visual Studio.

Transcript:
Hi I'm Catherine Wilhelmsen and I've got a SQL Prompt tip for you - actually I’ll give you three tips. Today let's take a look at the results grid features. If you execute a query and right-click in the results grid you will see three new options: copy as IN clause, script is INSERT, and open in Excel.

Now if you ever select results like this and you want to copy and use them in an IN clause like this you probably know how time consuming it can be to add all those quotation marks and commas, escaping special characters, and so on - but not anymore.

All you have to do is select the values or the entire column, right click and choose ‘copy as IN clause’. Paste this and you'll see that everything has been sorted for you, including the escaping of special characters.

Now maybe you want to save these results. SQL Prompt makes it easy for you as well. Select the results, right click and choose ‘script as INSERT. This will open up this new query window for you with the create table insert into and drop table statements. Now just imagine the time saved from having all of this automated instead of having to write this by hand.

Other times you might want to share the results with business users or business analysts and we all know how much they love Excel right? All you have to do is right-click and choose ‘open in Excel’ and there you go now you have spreadsheet that you can save share and send to other users. Now if you're a lazy, also known as an efficient developer like me I hope you'll find these tips as useful as I do. Get the latest SQL Prompt update and try it for yourself.

[sushantsurankar]

Hi. Every time I use this function (Script as INSERT) on a result set that contains datetime values, then it adds character 'T' between date and time. E.g. Date in result set is '2019-06-01 00:00:00.000' and after using Script as Insert option on this, then code given in new window is as below :


CREATE TABLE #temptable ( [DATE OF SERVICE] datetime )
INSERT INTO #temptable
VALUES
( N'2019-06-01T00:00:00' )

DROP TABLE #temptable




Notice that there is a T added in the values clause. Due to this I get hard time to troubleshoot further issues occurred. Is there any setting due to which it adds a T if yes please let me know how I can prevent it from inserting or is this a bug ? Awaiting for your response.