Cool Tips‎ > ‎Scripting SQL Server‎ > ‎

Auto-Create Insert Into Table TSQL Templates

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Tested

Credits:

Author: Unknown
Date:    10 Jun 2020

Description

For all Tables in a database, generate TSQL templates to INSERT into each table

Code



SELECT '/* '+qualifiedName+' */' +CHAR(13)+CHAR(10)+REPLACE( REPLACE( REPLACE( REPLACE( STUFF(ColumnsClause, /*delete final comma line-terminator*/ LEN(ColumnsClause)-1-CHARINDEX('|,|', REVERSE(ColumnsClause)+'|'),3 ,'')+ STUFF(ValuesClause, /*delete final comma line-terminator*/ LEN(ValuesClause)-1-CHARINDEX('|,|', REVERSE(ValuesClause)+'|'),3 ,'') ,'\n',CHAR(13)+CHAR(10)) ,'|,|',',') /*put in new-lines and convert token to comma*/ ,'&lt;','<') ,'&gt;','>') AS Template FROM (SELECT so.name AS Name, OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName, 'INSERT INTO '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID)) +'.'+QUOTENAME(so.name) +COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '') +'\n (' +REPLACE(COALESCE( ( SELECT '\n '+sp.name+'|,|' +COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/','') FROM sys.columns sp INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID LEFT OUTER JOIN sys.extended_properties ep ON sp.object_id = ep.major_ID AND sp.column_ID = minor_ID AND class=1 WHERE sp.object_ID=so.object_ID AND column_ID>0 ORDER BY column_ID FOR XML PATH('') ),'1') ,',||', '') AS [columnsClause], '\n )\nVALUES(' +REPLACE(COALESCE( ( SELECT REPLACE ('\n |delim;<' +REPLACE( COALESCE( CONVERT(VARCHAR(MAX),value) ,REPLACE(sp.name,'@','') ) ,',','') +','+t.name+','+'>|delim;'+'|,|' +COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'|delim;', CASE WHEN t.name IN ('char', 'varchar','nchar' ,'nvarchar','text','ntext' ,'datetime','date') THEN '''' WHEN t.precision=0 THEN '''' ELSE '' END) FROM sys.columns sp INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID LEFT OUTER JOIN sys.extended_properties ep ON sp.object_id = ep.major_ID AND sp.column_ID = minor_ID AND class=1 WHERE sp.object_ID=so.object_ID AND column_ID>0 ORDER BY column_ID FOR XML PATH('') ),'1') ,',||', '') +'\n )' [ValuesClause] FROM sys.objects so LEFT OUTER JOIN sys.extended_properties ep /* get any extended properties */ ON ep.name LIKE 'MS_Description' AND major_ID=so.object_ID AND minor_ID=0 --for the table WHERE OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f ORDER BY name





ċ
Andy Hughes,
Jun 10, 2020, 5:20 PM
Comments