T-SQL – Generate INSERT List for Non-Nullable Columns Only

identityinsertssmst-sql

I am currently using SSMS 2016 as client and have an Azure SQL database (also 2008 r2).

But I am looking for a way to create a custom template to have an insert statement prepared with only the fields that have 'non-null'

enter image description here

If I right click on a table and generate an insert into statement, I get a really long list of all possible columns. I would like to see the bare minimum of columns which needs to be filled. Is there a way to generate such a sql script for ssms? Or is this only possible with other db tooling?

I have a table with over 30 columns and about 90% is allowed to be null, so I prefer my generated insert statement to only include the 10% of columns which I really need to fill in.

Best Answer

You can't change the way the UI functions, you'll have to write that kind of code yourself, sorry. But in the simple case, this is easy enough to generate on your own with sys.dm_exec_describe_first_result_set (save this code as a template or snippet):

DECLARE @table nvarchar(261) = N'dbo.YourTableName';

;WITH x AS 
(
  SELECT name, system_type_name, 
    column_ordinal = ROW_NUMBER() OVER (ORDER BY column_ordinal)
  FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM ' + @table, N'', 0)
  WHERE is_nullable = 0
    AND is_identity_column = 0
    AND is_computed_column = 0
)
SELECT N'INSERT ' + @table + N'(', N'--', column_ordinal = 0
UNION ALL
SELECT CHAR(13) + CHAR(10) + CASE column_ordinal 
  WHEN 1 THEN N'' ELSE N',' END + QUOTENAME(name), 
  N'--', column_ordinal
FROM x
UNION ALL
SELECT N'
) VALUES (', N'--', 1000
UNION ALL
SELECT CHAR(13) + CHAR(10) + CASE column_ordinal 
  WHEN 1 THEN N'' ELSE N',' END
  + N'<' + name + N', ' +system_type_name + N',>',
  N'--', 1000 + column_ordinal
FROM x
UNION ALL
SELECT N'
);', N'--', 10000
ORDER BY column_ordinal;

This leaves out the identity column and any computed columns, but the view doesn't tell you about other things like not null columns with defaults or rowguidcol columns, which should also stay out of your statement.

The following solution is probably more appropriate in general since you are still using SQL Server 2008 R2 (that view was introduced in SQL Server 2012). It handles rowguidcol and default values but is a lot more verbose because of the work required to generate friendly-looking types.

DECLARE @table nvarchar(261) = N'dbo.YourTableName';

;WITH x AS 
(
  SELECT [column] = c.name, [type] = t.name, t.system_type_id, 
    t.user_type_id, c.max_length, c.precision, c.scale, 
    column_ordinal = ROW_NUMBER() OVER (ORDER BY c.column_id)
  FROM sys.columns AS c 
  INNER JOIN sys.types AS t
    ON c.system_type_id = t.system_type_id
   AND t.user_type_id = CASE -- filter out false sysname
     WHEN c.system_type_id = 231 THEN CASE 
       WHEN c.max_length = 256 THEN c.user_type_id 
       ELSE c.system_type_id END
     ELSE c.user_type_id END
  WHERE [object_id] = OBJECT_ID(@table)
    AND c.is_nullable       = 0 
    AND c.is_identity       = 0 
    AND c.is_computed       = 0
    AND c.is_rowguidcol     = 0 
    AND c.default_object_id = 0
)
SELECT N'INSERT ' + @table + N'(', N'--', column_ordinal = 0
UNION ALL 
SELECT CASE column_ordinal WHEN 1 THEN N'' ELSE N',' END
    + QUOTENAME([column]), N'--', column_ordinal 
  FROM x
UNION ALL SELECT N') VALUES(', N'--', 1000
UNION ALL
SELECT CASE column_ordinal WHEN 1 THEN N'' ELSE N',' END
    + N'<' + [column] + N',' + [type] + CASE 
    WHEN system_type_id IN (106,108)
    THEN N'(' + CONVERT(varchar(11), precision)
       + N',' + CONVERT(varchar(11), scale) + N')'
    WHEN system_type_id IN (59,62)
    THEN N'(' + CONVERT(varchar(11), precision) + N')'
    WHEN system_type_id IN (41,42,43)
    THEN N'(' + CONVERT(varchar(11), scale) + N')'
    WHEN system_type_id IN (167,175,239,231,165,173) AND user_type_id <> 256
    THEN N'(' + CASE max_length WHEN -1 THEN N'max' ELSE 
      CONVERT(varchar(11), max_length / CASE 
        WHEN system_type_id IN (231,239) THEN 2 ELSE 1 END) END + ')'
    ELSE N''
   END
    + N',>', N'--', 1000 + column_ordinal
  FROM x
UNION ALL SELECT N');', N'--', 10000
ORDER BY column_ordinal;

Note that in both cases I tried to match the output of SSMS exactly with the exception of every single tab and carriage return. I don't think their output makes sense in all aspects, but you can tailor it to your liking.

Related Question