I think I love the solution from @Werner Sauer, however today I needed to do it on a pre-2017 sql server -- no string_agg()! Here's what I landed with:

/* Dynamic Insert Statement generator */

DECLARE @SchemaName SYSNAME = 'dbo';
DECLARE @TableName SYSNAME = 'myTableName';

SET NOCOUNT ON;
SET TEXTSIZE 2147483647;

DECLARE @ColumnList NVARCHAR(MAX) = '';
DECLARE @ValueList NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);

-- Store column metadata in a table variable
DECLARE @Cols TABLE (
    ColumnName SYSNAME,
    DataType SYSNAME,
    ColumnId INT
);

INSERT INTO @Cols (ColumnName, DataType, ColumnId)
SELECT 
    c.name AS ColumnName,
    t.name AS DataType,
    c.column_id
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName));

-- Build comma-separated column names
SELECT @ColumnList = STUFF((
    SELECT ', ' + QUOTENAME(ColumnName)
    FROM @Cols
    ORDER BY ColumnId
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- Build concatenation logic for each column
SELECT @ValueList = STUFF((
    SELECT ' + '','' + ' +
        CASE 
            WHEN DataType IN ('char','nchar','varchar','nvarchar','text','ntext')
                THEN 'COALESCE('''''''' + REPLACE(' + QUOTENAME(ColumnName) + ', '''''''', '''''''''''') + '''''''', ''NULL'')'
            WHEN DataType IN ('datetime','smalldatetime','date','datetime2','time')
                THEN 'COALESCE('''''''' + CONVERT(VARCHAR, ' + QUOTENAME(ColumnName) + ', 121) + '''''''', ''NULL'')'
            ELSE 'COALESCE(CAST(' + QUOTENAME(ColumnName) + ' AS VARCHAR), ''NULL'')'
        END
    FROM @Cols
    ORDER BY ColumnId
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 8, ''); -- remove first " + ',' + "

-- Build the final SQL
SET @SQL = 
    'SELECT ''INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +
    ' (' + @ColumnList + ') VALUES ('' + ' + @ValueList + ' + '') ;'' AS InsertStatement ' + CHAR(13) +
    'FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ';';

-- Execute the generated SQL
EXEC sp_executesql @SQL;