Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Insert Statement Builder - SQL Server

declare
     @TableNameExt      nvarchar(500)   = '[dbo].[OrderHeader]'
    ,@IdentityInsert    bit             = 0


;with cte as (
    select
         *
         ,TableNameExt = '[' + SchemaName + '].[' + TableName + ']'
         ,ColumnNameExt = '[' + ColumnName + ']'
         ,MaxLen = max(len(ColumnName)) over (partition by SchemaName, TableName) + 2
    from
        dbo.DbLayout
    where 1=1
        and '[' + SchemaName + '].[' + TableName + ']' = @TableNameExt
    )
select distinct
     TableNameExt
    ,Sort1 = 20
    ,Sort2 = 10
    ,SqlCode = '/*--- ' + TableNameExt + ' ---*/'
from
    cte
union
select distinct
     TableNameExt
    ,Sort1 = 30
    ,Sort2 = 10
    ,SqlCode = 'set identity_insert ' + TableNameExt + ' on'
from
    cte
where 1=1
    and cte.IsIdentity = 1
    and @IdentityInsert = 1
union
select distinct
     TableNameExt
    ,Sort1 = 35
    ,Sort2 = 10
    ,SqlCode = ''
from
    cte
where 1=1
    and cte.IsIdentity = 1
    and @IdentityInsert = 1
union
select distinct
     TableNameExt
    ,Sort1      = 40
    ,Sort2 = 10
    ,SqlCode    = 'insert into ' + TableNameExt + ' ('
from
    cte
union
select distinct
     TableNameExt
    ,Sort1      = 50
    ,Sort2      = ColumnOrder
    ,SqlCode    = '    ' + case when (row_number() over (order by ColumnOrder)) = 1 then ' ' else ',' end + ColumnNameExt
from
    cte
where 1=1
    and (cte.IsIdentity = 0 or @IdentityInsert = 1)
    and cte.IsCalculated = 0
union
select distinct
     TableNameExt
    ,Sort1      = 55
    ,Sort2 = 10
    ,SqlCode    = '    )'
from
    cte
union
select distinct
     TableNameExt
    ,Sort1      = 60
    ,Sort2 = 10
    ,SqlCode    = 'select'
from
    cte
union
select distinct
     TableNameExt
    ,Sort1      = 70
    ,Sort2 = ColumnOrder
    ,SqlCode    = '    ' 
                    + case when (row_number() over (order by ColumnOrder)) = 1 then ' ' else ',' end 
                    + ColumnNameExt 
                    + REPLICATE(' ', MaxLen - len(ColumnNameExt) + 2) 
                    + '= ' 
                    + case ColumnName   
                        when 'CreatedBy' then '''system'''
                        when 'UpdatedBy' then '''system'''
                        when 'CreatedOn' then 'getutcdate()'
                        when 'UpdatedOn' then 'getutcdate()'
                        else 'null'+ ' -- ' + cte.DataType + ' ' + case when cte.IsNullable = 1 then '' else 'not ' end + 'null'
                        end
                    
from
    cte
where 1=1
    and (cte.IsIdentity = 0 or @IdentityInsert = 1)
    and cte.IsCalculated = 0

union
select distinct
     TableNameExt
    ,Sort1 = 75
    ,Sort2 = 10
    ,SqlCode = ''
from
    cte
union
select distinct
     TableNameExt
    ,Sort1 = 80
    ,Sort2 = 10
    ,SqlCode = 'set identity_insert ' + TableNameExt + ' off'
from
    cte
where 1=1
    and cte.IsIdentity = 1
    and @IdentityInsert = 1
union
select distinct
     TableNameExt
    ,Sort1 = 90
    ,Sort2 = 10
    ,SqlCode = ''
from
    cte
where 1=1
    and cte.IsIdentity = 1
    and @IdentityInsert = 1
order by
     TableNameExt
    ,Sort1