Q:

Tool to export result set from SQL to Insert statements?

I would like to export a ad hoc Select query result sets from SQL Server to be exported directly as Insert Statements.
I would love to see a Save As option "Insert.." along with the other current available options (csv, txt) when you right-click in SSMS. I am not exporting from an existing physical table and I have no permissions to create new tables so the options to script physical tables are not an option for me.
I have to script either from temporary tables or from the result set in the query window.
Right now I can export to csv and then import that file into another table but that's time consuming for repetitive work. 
The tool has to create proper Inserts and understand the data types when it creates values for NULL values.

A:

Personally, I would just write a select against the table and generate the inserts myself.  Piece of cake.
For example:
SELECT  'insert into [pubs].[dbo].[authors](
                    [au_id], 
                    [au_lname], 
                    [au_fname], 
                    [phone], 
                    [address], 
                    [city], 
                    [state], 
                    [zip], 
                    [contract])
    values( ''' + 
    [au_id] + ''', ''' + 
    [au_lname] + ''', ''' +
    [au_fname] + ''', ''' +
    [phone] + ''', ''' +
    [address] + ''', ''' +
    [city] + ''', ''' +
    [state] + ''', ''' +
    [zip] + ''', ' +
    cast([contract] as nvarchar) + ');'
FROM    [pubs].[dbo].[authors]

will produce
insert into [pubs].[dbo].[authors](
                    [au_id], 
                    [au_lname], 
                    [au_fname], 
                    [phone], 
                    [address], 
                    [city], 
                    [state], 
                    [zip], 
                    [contract])
    values( '172-32-1176', 'White', 'Johnson', '408 496-7223', '10932 Bigge Rd.', 'Menlo Park', 'CA', '94025', 1);
insert into [pubs].[dbo].[authors](
                    [au_id], 
                    [au_lname], 
                    [au_fname], 
                    [phone], 
                    [address], 
                    [city], 
                    [state], 
                    [zip], 
                    [contract])
    values( '213-46-8915', 'Green', 'Marjorie', '415 986-7020', '309 63rd St. #411', 'Oakland', 'CA', '94618', 1);
... etc ...

A couple pitfalls:

Don't forget to wrap your single
quotes
This assumes a clean database and
is not SQL Injection safe.

A:

take a look at the SSMS Tools Pack add in for SSMS which allows you to do just what you need.

A:

ATTENTION!!! AS IS. At the start of script you can see example how to use procedure. Of course you can make INSERT expresion if you need or add DataTypes for your required conversion.
The script's result is concated SELECT expresions with UNION ALL.
Be careful with collation of your data base. I didn't test other collation more than I need.
For long lenght fields I recomend use [Save Result As..] in Result grid instead Copy. Bacause you may get cutted script. 
/*
USE AdventureWorks2012
GO

IF OBJECT_ID('tempdb..#PersonTbl') IS NOT NULL
    DROP TABLE #PersonTbl;
GO

SELECT TOP (100)
        BusinessEntityID
      , PersonType
      , NameStyle
      , Title
      , FirstName
      , MiddleName
      , LastName
      , Suffix
      , EmailPromotion
      , CONVERT(NVARCHAR(MAX), AdditionalContactInfo) AS [AdditionalContactInfo]
      , CONVERT(NVARCHAR(MAX), Demographics) AS [Demographics]
      , rowguid
      , ModifiedDate
INTO    #PersonTbl
FROM    Person.Person

 EXEC dbo.p_GetTableAsSqlText
    @table_name = N'#PersonTbl'

EXEC dbo.p_GetTableAsSqlText
    @table_name = N'Person'
    , @table_owner = N'Person'
*/
    /*********************************************************************************************/
IF OBJECT_ID('dbo.p_GetTableAsSqlText', 'P') IS NOT NULL
    DROP PROCEDURE dbo.p_GetTableAsSqlText
GO

CREATE PROCEDURE [dbo].[p_GetTableAsSqlText]
    @table_name NVARCHAR(384) /*= 'Person'|'#Person'*/
  , @database_name NVARCHAR(384) = NULL /*= 'AdventureWorks2012'*/
  , @table_owner NVARCHAR(384) = NULL /*= 'Person'|'dbo'*/
 /*WITH ENCRYPTION, RECOMPILE, EXECUTE AS CALLER|SELF|OWNER| 'user_name'*/
AS /*OLEKSANDR PAVLENKO p_GetTableAsSqlText ver.2016.10.11.1*/
    DECLARE @isTemporaryTable BIT = 0

/*[DATABASE NAME]*/
    IF (PATINDEX('#%', @table_name) <> 0)
        BEGIN
            SELECT  @database_name = DB_NAME(2) /*2 - 'tempdb'*/
                  , @isTemporaryTable = 1
        END
    ELSE
        SET @database_name = COALESCE(@database_name, DB_NAME())
/*END [DATABASE NAME]*/

/*[SCHEMA]*/
    SET @table_owner = COALESCE(@table_owner, SCHEMA_NAME())

    DECLARE @database_nameQuoted NVARCHAR(384) = QUOTENAME(@database_name, '')
    DECLARE @table_ownerQuoted NVARCHAR(384) = QUOTENAME(@table_owner, '')
    DECLARE @table_nameQuoted NVARCHAR(384) = QUOTENAME(@table_name, '')

    DECLARE @full_table_name NVARCHAR(769)
 /*384 + 1 + 384*/
    DECLARE @table_id INT

    SET @full_table_name = CONCAT(@database_nameQuoted, '.', @table_ownerQuoted, '.', @table_nameQuoted)
    SET @table_id = OBJECT_ID(@full_table_name)

    CREATE TABLE #ColumnTbl
        (
         ColumnId INT
       , ColName sysname COLLATE DATABASE_DEFAULT
       , TypeId TINYINT
       , TypeName sysname COLLATE DATABASE_DEFAULT
       , TypeMaxLength INT
        ) 

    DECLARE @dynSql NVARCHAR(MAX) = CONCAT('
INSERT INTO #ColumnTbl
SELECT  ISC.ORDINAL_POSITION AS [ColumnId]
      , ISC.COLUMN_NAME AS [ColName]
      , T.system_type_id AS [TypeId]
      , ISC.DATA_TYPE AS [TypeName]
      , ISC.CHARACTER_MAXIMUM_LENGTH AS [TypeMaxLength]
FROM    ', @database_name, '.INFORMATION_SCHEMA.COLUMNS AS [ISC]
        INNER JOIN ', @database_name, '.sys.objects AS [O] ON ISC.TABLE_NAME = O.name
        INNER JOIN ', @database_name, '.sys.types AS [T] ON ISC.DATA_TYPE = T.name
WHERE   ISC.TABLE_CATALOG = "', @database_name, '"
        AND ISC.TABLE_SCHEMA = "', @table_owner, '"
        AND O.object_id = ', @table_id)

    IF (@isTemporaryTable = 0)
        SET @dynSql = CONCAT(@dynSql, '
        AND ISC.TABLE_NAME = "', @table_name, '"
')
    ELSE
        SET @dynSql = CONCAT(@dynSql, '
        AND ISC.TABLE_NAME LIKE "', @table_name, '%"
')

    SET @dynSql = REPLACE(@dynSql, '"', '''')
    EXEC(@dynSql)

    DECLARE @columnNamesSeparated NVARCHAR(MAX) = SUBSTRING((SELECT ', [' + C.ColName + ']' AS [text()]
                                                             FROM   #ColumnTbl AS [C]
                                                             ORDER BY C.ColumnId
                                                            FOR
                                                             XML PATH('')
                                                            ), 2, 4000)
    --SELECT  @columnNamesSeparated

    DECLARE @columnNamesSeparatedWithTypes NVARCHAR(MAX) = SUBSTRING((SELECT    '+", " + "CONVERT(' + (CASE C.TypeId
                                                                                                         WHEN 231 /*NVARCHAR*/
                                                                                                         THEN CONCAT(C.TypeName, '(',
                                                                                                                     (CASE WHEN C.TypeMaxLength = -1 THEN 'MAX'
                                                                                                                           ELSE CONVERT(NVARCHAR(MAX), C.TypeMaxLength)
                                                                                                                      END), ')')
                                                                                                         WHEN 239 /*NCHAR*/
                                                                                                         THEN CONCAT(C.TypeName, '(', C.TypeMaxLength, ')') 
                                                                                                      /*WHEN -1 /*XML*/ THEN '(MAX)'*/
                                                                                                         ELSE C.TypeName
                                                                                                       END) + ', "+ COALESCE('
                                                                                + (CASE C.TypeId
                                                                                     WHEN 56 /*INT*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
                                                                                     WHEN 40 /*DATE*/
                                                                                     THEN 'N"""" + CONVERT(NVARCHAR(MAX), [' + C.ColName + '], 101) + """"'
                                                                                     WHEN 60 /*MONEY*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
                                                                                     WHEN 61 /*DATETIME*/
                                                                                     THEN '"""" + CONVERT(NVARCHAR(MAX), [' + C.ColName + '], 21) + """"'
                                                                                     WHEN 104 /*BIT*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
                                                                                     WHEN 106 /*DECIMAL*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
                                                                                     WHEN 127 /*BIGINT*/ THEN 'CONVERT(NVARCHAR(MAX), [' + C.ColName + '])'
                                                                                     WHEN 189 /*TIMESTAMP*/
                                                                                     THEN 'N"""" + CONVERT(NVARCHAR(MAX), SUBSTRING([' + C.ColName
                                                                                          + '], 1, 8000), 1) + """"'
                                                                                     WHEN 241 /*XML*/
                                                                                     THEN '"""" + CONVERT(NVARCHAR(MAX), [' + C.ColName + ']) + """"'
                                                                                     ELSE 'N"""" + CONVERT(NVARCHAR(MAX), REPLACE([' + C.ColName
                                                                                          + '], """", """""")) + """"'
                                                                                   END) + ' , "NULL") + ") AS [' + C.ColName + ']"' + CHAR(10) COLLATE DATABASE_DEFAULT AS [text()]
                                                                      FROM      #ColumnTbl AS [C]
                                                                      ORDER BY  C.ColumnId
                                                                     FOR
                                                                      XML PATH('')
                                                                     ), 9, 100000)

/*SELECT @columnNamesSeparated, @full_table_name*/
    DECLARE @dynSqlText NVARCHAR(MAX) = CONCAT(N'
SELECT (CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT   1 )) = 1 THEN "
/*INSERT INTO ', @full_table_name, '
     (', @columnNamesSeparated, '
     )*/', '
SELECT T.* /*INTO #ResultTbl*/
FROM (
"
                ELSE "UNION ALL "
                END) + "SELECT "+ ', @columnNamesSeparatedWithTypes, ' FROM ', @full_table_name)
    SET @dynSqlText = CONCAT(@dynSqlText, ' UNION ALL SELECT ") AS [T]

/*SELECT *
FROM #ResultTbl*/
"')

    SET @dynSqlText = REPLACE(@dynSqlText, '"', '''')
    --SELECT  @dynSqlText AS [XML_F52E2B61-18A1-11d1-B105-00805F49916B]
    EXEC(@dynSqlText)

    IF OBJECT_ID('tempdb..#ColumnTbl') IS NOT NULL
        DROP TABLE #ColumnTbl;
GO

