configT.SsasDmvTableImport_ImportSql - V

type: V ( view ), modify_date: 2021-10-03 16:16:35

RepoObject_guid: DAE6A70A-8A06-EC11-8515-A81E8446D5B0

Description

obsolet

  • we could truncate once and import all

  • or we could use the [isCurrentImport] and loop through aktive databasename and import them one by one

todo:

  • ExecPersistSql only, if persistence exists

Examples

Entity Diagram

entity-configt.ssasdmvtableimport_importsql

Columns

Table 1. Columns of configT.SsasDmvTableImport_ImportSql - V
PK Column Name Data Type NULL? ID

nvarchar(128)

NOT NULL

nvarchar(4000)

NOT NULL

nvarchar(161)

NOT NULL

bit

NOT NULL

bit

NOT NULL

nvarchar(128)

NOT NULL

nvarchar(156)

NOT NULL

Foreign Key Diagram

entity_1_1_fk-configt.ssasdmvtableimport_importsql

References

Object Reference Diagram - 1 1

entity_1_1_objectref-configt.ssasdmvtableimport_importsql

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-configt.ssasdmvtableimport_importsql

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-configt.ssasdmvtableimport_importsql

Column Reference Diagram

entity_1_1_colref-configt.ssasdmvtableimport_importsql

Indexes

idx_SsasDmvTableImport_ImportSql__1

Column Details

_

databasename

databasename

nvarchar(128)

NOT NULL

DeleteInsertSql

DeleteInsertSql

nvarchar(4000)

NOT NULL

ExecPersistSql

ExecPersistSql

nvarchar(161)

NOT NULL

isActive

isActive

bit

NOT NULL

isCurrentImport

isCurrentImport

bit

NOT NULL

LinkedServer

LinkedServer

nvarchar(128)

NOT NULL

truncateSql

truncateSql

nvarchar(156)

NOT NULL

sql_modules_definition

configT.SsasDmvTableImport_ImportSql - V script
/*
<<property_start>>Description
obsolet

* we could truncate once and import all
* or we could use the [isCurrentImport] and loop through aktive databasename and import them one by one

todo:

* ExecPersistSql only, if persistence exists
<<property_end>>
*/
CREATE   View [configT].[SsasDmvTableImport_ImportSql]
As
Select
    T2.databasename
  , T2.LinkedServer
  , T2.isActive
  , T2.isCurrentImport
  , DeleteInsertSql = Concat (
                                 'DELETE '
                               , Char ( 13 ) + Char ( 10 )
                               , 'ssas.'
                               , T1.DmvTableName
                               , Char ( 13 ) + Char ( 10 )
                               , 'WHERE databasename = ''' + T2.databasename + ''''
                               , Char ( 13 ) + Char ( 10 )
                               , 'GO'
                               , Char ( 13 ) + Char ( 10 )
                               , Char ( 13 ) + Char ( 10 )
                               , 'Insert Into '
                               , Char ( 13 ) + Char ( 10 )
                               , 'ssas.'
                               , T1.DmvTableName
                               , Char ( 13 ) + Char ( 10 )
                               , '('
                               , Char ( 13 ) + Char ( 10 )
                               , 'databasename'
                               , Char ( 13 ) + Char ( 10 )
                               , ', '
                               , T1.DmvColumnList
                               , Char ( 13 ) + Char ( 10 )
                               , ')'
                               , Char ( 13 ) + Char ( 10 )
                               , 'Select'
                               , Char ( 13 ) + Char ( 10 )
                               , '''' + T2.databasename + ''''
                               , Char ( 13 ) + Char ( 10 )
                               , ', '
                               , T1.DmvColumnList
                               , Char ( 13 ) + Char ( 10 )
                               , 'From'
                               , Char ( 13 ) + Char ( 10 )
                               , 'OpenQuery'
                               , Char ( 13 ) + Char ( 10 )
                               , '( '
                               , T2.LinkedServer
                               , ', '
                               , Char ( 13 ) + Char ( 10 )
                               , '''Select '
                               , Char ( 13 ) + Char ( 10 )
                               , '  '
                               , T1.DmvColumnList
                               , Char ( 13 ) + Char ( 10 )
                               , 'From $SYSTEM.'
                               , T1.DmvTableName
                               , ''''
                               , Char ( 13 ) + Char ( 10 )
                               , ')'
                               , Char ( 13 ) + Char ( 10 )
                               , 'GO'
                               , Char ( 13 ) + Char ( 10 )
                             )
  , ExecPersistSql     = Concat (
                                 'EXECUTE '
                               , 'ssas.usp_PERSIST_'
                               , T1.DmvTableName
                               , '_T'
                               , Char ( 13 ) + Char ( 10 )
                               , 'GO'
                               , Char ( 13 ) + Char ( 10 )
                             )
  , truncateSql     = Concat (
                                 'TRUNCATE TABLE '
                               , Char ( 13 ) + Char ( 10 )
                               , 'ssas.'
                               , T1.DmvTableName
                               , Char ( 13 ) + Char ( 10 )
                               , 'GO'
                               , Char ( 13 ) + Char ( 10 )
                             )
From
    configT.SsasDmvTableImport_DmvColumnList As T1
    --there could be more than one isActive database
    Cross Join config.SsasDatabasename       As T2
Where
    T2.isActive = 1