Script table contents as INSERT statements

script_table_as_insert.sql

DECLARE @object_id int=OBJECT_ID('schema_name.table_name');

DECLARE @name nvarchar(max)=(SELECT QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name]) FROM sys.objects WHERE [object_id]=@object_id),
        @cols nvarchar(max)=N'',
        @query nvarchar(max)=N'',
        @pagesize varchar(10)=N'100';


WITH cte AS (
    SELECT c.column_id, c.[name], t.[name] AS [type]
    FROM sys.columns AS c
    INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id
    WHERE c.[object_id]=@object_id
      AND c.is_computed=0
      AND c.column_id NOT IN (SELECT column_id FROM sys.identity_columns WHERE [object_id]=@object_id))

SELECT @cols=@cols+', '+QUOTENAME([name]),
       @query=@query+'+'', ''+'+(CASE WHEN [type] LIKE '%int' OR [type]='bit' THEN 'ISNULL(CAST('+QUOTENAME([name])+' AS nvarchar(max)), N''NULL'')'
                                      WHEN [type] IN ('numeric', 'decimal', 'float', 'real', 'money', 'smallmoney') THEN 'ISNULL(CAST('+QUOTENAME([name])+' AS nvarchar(max)), N''NULL'')'
                                      WHEN [type]='date' THEN 'ISNULL(N''{d ''''''+CONVERT(nvarchar(10), '+QUOTENAME([name])+', 121)+N''''''}'', N''NULL'')'
                                      WHEN [type] LIKE 'datetime%' THEN 'ISNULL(N''{ts ''''''+CONVERT(nvarchar(20), '+QUOTENAME([name])+', 121)+N''''''}'', N''NULL'')'
                                      ELSE 'ISNULL(N''''''''+REPLACE('+QUOTENAME([name])+', N'''', N'''''''')+N'''''''', N''NULL'')' END)
FROM cte
ORDER BY column_id;


SELECT @cols=SUBSTRING(@cols, 3, LEN(@cols)),
       @query='
SELECT (CASE WHEN rn%'+@pagesize+'=1 THEN ''
INSERT INTO '+@name+' ('+@cols+')
VALUES '' ELSE '''' END)+
       ''       (''+s+'')''+
       (CASE WHEN LEAD(rn%'+@pagesize+', 1, 1) OVER (ORDER BY rn)=1 THEN '';'' ELSE '','' END) AS [sql]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn,
           '+SUBSTRING(@query, 7, LEN(@query))+' AS s
    FROM '+@name+N'
    ) AS x';


--PRINT @query;
EXECUTE sys.sp_executesql @query;