Uso de Sql dinámico en la creación de scripts BIML (I)
Para la aceleración de muchas tareas en que se repiten determinados patrones es muy útil el uso de sql dinámico. El uso de Sql dinámico permite la rápida generación de código que puede ser aplicado en múltiples situaciones en que se ha de aplicar a muchas tablas una misma operación. Además podemos aplicar sql dinámico para la creación de código BIML el cual genera a su vez procesos ETL en SSIS.
Veamos un ejemplo.
Proponemos crear un código dinámico que nos permita generar un ETL que nos duplique todas o parte de las tablas de la base datos para crear un data warehouse.
Para ello utilizaremos las vistas del sistema que contienen toda la información de las tablas para generar un CREATE que las genera y que podemos encontrar aquí:
Utilizaremos el siguiente script que utiliza un fragmento de código que recorre todas las vistas del sistema donde se almacenan los campos y constraints de cada tabla y genera una cadena de texto con las instrucciones CREATE que si la ejecutamos da lugar a una copia de la tabla que buscamos duplicar. Colocamos todas las instrucciones en una tabla temporal que almacene todas las instrucciones para cada una de las tablas de la base datos en que se ejecute el script:
Lanzamos este script en la base de datos de AdventureworksDW2012 con ello generamos la siguiente salida:
Donde para cada una de las tablas se genera esta instrucción:
DECLARE @Lista VARCHAR(3000)=''declare @table varchar(100)
set @table ='DimAccount'
declare @sql table(s varchar(1000), id int identity)
insert into @sql(s) values ('create table [' + @table + '] (')
insert into @sql(s)
select
' ['+column_name+'] ' +
data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=@table
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(@table) as varchar) + ',' +
cast(ident_incr(@table) as varchar) + ')'
else ''
end + ' ' +
( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
from information_schema.columns where table_name = @table
order by ordinal_position
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'
if ( @pkname is not null ) begin
insert into @sql(s) values(' PRIMARY KEY (')
insert into @sql(s)
select ' ['+COLUMN_NAME+'],' from information_schema.key_column_usage
where constraint_name = @pkname
order by ordinal_position
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values (' )')
end
else begin
update @sql set s=left(s,len(s)-1) where id=@@identity
end
insert into @sql(s) values( ')' )
SELECT @Lista = @Lista + s from @sql
SELECT @Lista
Que si a su vez la ejecutamos, tenemos una instrucción que generara dicha tabla:
create table [DimAccount] (
[AccountKey] int IDENTITY(1,1) NOT NULL ,
[ParentAccountKey] int NULL ,
[AccountCodeAlternateKey] int NULL ,
[ParentAccountCodeAlternateKey] int NULL ,
[AccountDescription] nvarchar(50) NULL ,
[AccountType] nvarchar(50) NULL ,
[Operator] nvarchar(50) NULL ,
[CustomMembers] nvarchar(300) NULL ,
[ValueType] nvarchar(50) NULL ,
[CustomMemberOptions] nvarchar(200) NULL ,
PRIMARY KEY ( [AccountKey]))
En el artículo que sigue a este veremos como generar una script create para cada una de las tablas, o de un determinado dataset , y a partir de este, generar un script BIML con el que generar un ETL en SSIS que no permita duplicar cada tabla y rellenarla de datos a voluntad,