Script Out SQL Row Constructors

A while back I got tired of having to type out the long strings to script out rows from a table, so I built a snippet which does it for you. It’s almost illegible to read, but it determines the columns of the table you choose and builds a string to output each row. I'll admit that this could be further refined to add in some more precise data type handling (perhaps casting date fields to a certain varchar format, or handling more obscure data types), but for most every day purposes, this does just fine. Also, when you copy/paste the results into a query window, you'll have to manually cut out the first "union all" to get it to run. These omissions are just me being lazy, but there's no reason they can't be done, and if you want to make them or wrap this in a procedure, I encourage you to do so.

use 
go
set nocount on
go

declare @string nvarchar(max) = 
(
    --This creates a framework for each column in the chosen table, which then gets serialized and applied to each row of the selected table
    select ''' + '''
        + quotename(column_name) 
        + case when data_type in ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'date', 'time', 'datetime', 'datetime2', 'datetimeoffset')
               then ' = '' + isnull(quotename(cast(' 
                    + quotename(column_name) 
                    + ' as varchar(8000)), ''''''''), ''NULL'') + '', '
               else ' = '' + isnull(cast('
                    + quotename(column_name)
                    + ' as varchar(8000)), ''NULL'') + '', '
          end
    from .information_schema.columns
    where table_name = ''
        and column_name not in ('InsertDate', 'UpdateDate')
    for xml path('')
)

-- This dynamically selects those columns from the chosen table and with any optional where clause, and outputs the rows you can copy/paste into  query window
select @string = '
    select 
        ''union all select ' 
        + left(@string, len(@String) - 5) -- chop of leading ' + '
        + ' from .dbo. '
        + ' '

exec sp_executesql @string