Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Page History: Insert Statement Builder - SQL Server

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: Tue, Mar 03, 2015, 10:37 AM


The following script requires the dbo.DbLayout view, which can be found here.

declare
    @TableNameExt nvarchar(500) = '[dbo].[OrderHeader]'

;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
union
select distinct
     TableNameExt
    ,Sort1 = 35
    ,Sort2 = 10
    ,SqlCode = ''
from
    cte
where 1=1
    and cte.IsIdentity = 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 ColumnOrder = 1 then ' ' else ',' end + ColumnNameExt
from
    cte
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 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
                        end
                    
from
    cte

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
union
select distinct
     TableNameExt
    ,Sort1 = 90
    ,Sort2 = 10
    ,SqlCode = ''
from
    cte
where 1=1
    and cte.IsIdentity = 1

order by
     TableNameExt
    ,Sort1

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.