Simple way to creating the insert query for the table in SQL Server

Simple way to creating the insert query for the table in SQL Server

Declare @TableName sysname = 'MemberRegistration'

Declare @result varchar(max) = ''

Declare @StartString varchar(max) = 'Insert into '+@TableName+''

SELECT

       @result = @result + '

       [' + ColumnName + '],' + +''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @StartString + '

(' + @result + ')

values ('

SELECT

       @result = @result + '

       @' + ColumnName + ',' + +''

FROM (SELECT

       REPLACE(col.name, ' ', '_') ColumnName,

       column_id,

       (CASE

              WHEN (typ.name = 'nvarchar' OR

                     typ.name = 'varchar') THEN typ.name + '(' + CONVERT(NVARCHAR(10), (col.max_length / 2)) + '),'

              ELSE typ.name + ','

       END) AS ColumnType

FROM sys.columns col

JOIN sys.types typ

       ON col.system_type_id = typ.system_type_id

       AND col.user_type_id = typ.user_type_id

WHERE object_id = OBJECT_ID(@TableName)) t

ORDER BY column_id

SET @result = @result + ')'

SET @result = REPLACE(@result, ',)', ')')

print @result