martes, 24 de diciembre de 2013

SQL SERVER: GENERACIÓN DE INSERTS DE RESPALDO CONDICIONADOS.

GENERANDO INSERTS DE RESPALDO.

Mucho tiempo sin publicar, pero en fin, a lo que nos atañe. Para no explayarme sobre el contexto, lo que presento a continuación es un query para generar INSERTS de respaldo para las ocasiones en las que se requiera. SQL Server, al menos en su versión del 2008 en adelante tiene una forma de generar todos los INSERTS de una tabla, pero, al parecer no tiene una forma de filtrarlos de manera especifica para que solo se generen los INSERTS correspondientes a ciertas condiciones. La necesidad surgió debido a que en determinados escenarios requería ejecutar una depuración de determinada información, proceder a ejecutar ciertos procesos y después regresar la información a su estado original. Encontré un procedimiento almacenado escrito por Neeraj Prasad Sharma, solo requerí modificarlo para las necesidades de mi particular problema, y adecuarlo a un entorno donde no tengo permitido crear procedimientos almacenados ni tampoco debo usar respaldos en tablas temporales (algo que hubiera simplificado bastante la tarea). Realice dos versiones que tiene básicamente la misma función , ambas generan los INSERTS solo que una solo funciona posicionándose en la base de datos y la otra funciona sin problemas con varias bases de datos que estén en el mismo servidor... esto parece redundante, pero a veces es un mal necesario... y como sea fue interesante adaptarlo. Los querys se ejecutan como tal, pero, no creo que tengan ningún problema en adaptarlos a procedimientos almacenados si así lo requieren.

A modo de resumen y por cuestiones que detallare mas delante resumiré o me referiré al query como <GENERA INSERTS>. El query es el siguiente:

/*
Agradecimientos a Neeraj Prasad Sharma. Me base en gran parte en un store procedure ya elaborado por el, solo
lo adecue a las necesidades de mi escenario particular.

El cambio de nombre a las variables fue hecho para con el unico proposito de facilitar comprension. El
query original lo tome en su momento del siguiente link:
http://stackoverflow.com/questions/5065357/how-to-generate-an-insert-script-for-an-existing-sql-server-table-that-includes

Se puede encontrar tambien en :
http://www.sqlservercentral.com/scripts/scripting/94260/
*/
SET NOCOUNT ON

--VARIABLES-------------------------------------------------------
DECLARE @Esquema CHAR(3) = 'dbo'
DECLARE @Tabla VARCHAR(100) = 'DimAccount'
DECLARE @Where VARCHAR(MAX) = NULL
/*NOTAS WHERE
-En caso de ser un campo char, varchar o similares   -->   [' AND <campo>=''''<valor>'''' ']
-En caso de ser un campo int, smallint o similares   -->   [' AND <campo>=<valor>             ]
-En caso de ser un campo bit (donde <valor> = 0 | 1)-->    [' AND <campo>=<valor>             ]
*/
--VARIABLES DE ENTORNO--------------------------------------------
DECLARE @Query VARCHAR(MAX) = ''
DECLARE @Columnas TABLE (
  [N] SMALLINT
  ,Columna VARCHAR(Max)
  )
DECLARE @NoColumnas SMALLINT = 0
DECLARE @Ciclos SMALLINT = 1
DECLARE @InsertInto VARCHAR(MAX) = ''

IF (@Where IS NULL)
BEGIN
  SET @Where = ' '
END

INSERT INTO @Columnas
SELECT ORDINAL_POSITION
  ,COLUMN_NAME
FROM Information_schema.columns ISC
WHERE TABLE_SCHEMA = @Esquema
  AND TABLE_NAME = @Tabla
  AND COLUMN_NAME NOT IN (
    'SyncDestination'
    ,'PendingSyncDestination'
    ,'SkuID'
    ,'SaleCreditedto'
    )

SELECT @NoColumnas = MAX([N])
FROM @Columnas

WHILE (@Ciclos <= @NoColumnas)
BEGIN
  SELECT @InsertInto = @InsertInto + '[' + Columna + '],'
  FROM @Columnas
  WHERE [N] = @Ciclos

  SELECT @Query = @Query + ' +CASE WHEN [' + Columna + '] IS NULL THEN ''Null'' ELSE ''''''''+                             
                        REPLACE(CONVERT(VARCHAR(MAX),RTRIM([' + Columna + '])) ,'''''''',''''  )                             
                        +'''''''' END+' + ''','''
  FROM @Columnas
  WHERE [N] = @Ciclos

  SET @Ciclos = @Ciclos + 1
END

SELECT @Query = LEFT(@Query, LEN(@Query) - 4)

SELECT @InsertInto = SUBSTRING(@InsertInto, 0, LEN(@InsertInto))

SELECT @Query = ' SELECT  ' + '''INSERT INTO ' + @Esquema + '.' + @Tabla + '(' + @InsertInto + ')' + ' VALUES ( ' + '''' + '+' + @Query + '+' + ''')'' AS [BKINSERTS]' + ' FROM  ' + @Esquema + '.' + @Tabla + '(NOLOCK) ' + ' WHERE 1=1 ' + @Where

EXEC (@Query)

SET NOCOUNT OFF

Y su versión dinámica ligeramente adaptada es  <GENERA INSERTS DINAMICO>:

/*
Agradecimientos a Neeraj Prasad Sharma. Me base en gran parte en un store procedure ya elaborado por el, solo
lo adecue a las necesidades de mi escenario particular.

El cambio de nombre a las variables fue hecho para con el unico proposito de facilitar comprension. El
query original lo tome en su momento del siguiente link:
http://stackoverflow.com/questions/5065357/how-to-generate-an-insert-script-for-an-existing-sql-server-table-that-includes

Se puede encontrar tambien en :
http://www.sqlservercentral.com/scripts/scripting/94260/
*/
SET NOCOUNT ON

--VARIABLES-------------------------------------------------------
DECLARE @BaseDeDatos VARCHAR(50) = 'AdventureWorksDW2008R2'
DECLARE @Esquema CHAR(3) = 'dbo'
DECLARE @Tabla VARCHAR(100) = 'DimAccount'
DECLARE @Where VARCHAR(MAX) = ' AND AccountCodeAlternateKey>1100 '
/*NOTAS PARA WHERE
-En caso de ser un campo char, varchar o similares   -->   [' AND <campo>=''''<valor>'''' ']
-En caso de ser un campo int, smallint o similares   -->   [' AND <campo>=<valor>             ]
-En caso de ser un campo bit (donde <valor> = 0 | 1)-->    [' AND <campo>=<valor>             ]
*/
--VARIABLES DE ENTORNO--------------------------------------------
DECLARE @Query VARCHAR(MAX) = ''
DECLARE @BDActual VARCHAR(75) = (
    SELECT DB_NAME()
    )

IF (@Where IS NULL)
BEGIN
  SET @Where = ' '
END

SET @Query = '
DECLARE @Columnas  TABLE ([N] SMALLINT , Columna VARCHAR(Max) ) 
DECLARE @NoColumnas SMALLINT=0                             
DECLARE @Ciclos SMALLINT=1
DECLARE @InsertInto VARCHAR(MAX)='''' 
DECLARE @InnerQuery VARCHAR(MAX)=''''

USE ' + @BaseDeDatos + '
INSERT INTO @Columnas
SELECT
ORDINAL_POSITION,
COLUMN_NAME
FROM Information_schema.columns ISC
WHERE TABLE_SCHEMA=''' + @Esquema + ''' AND TABLE_NAME=''' + @Tabla + ''' AND TABLE_CATALOG=''' + @BaseDeDatos + ''' AND COLUMN_NAME NOT IN (''SyncDestination'',''PendingSyncDestination'',''SkuID'',''SaleCreditedto'')
ORDER BY ISC.ORDINAL_POSITION
USE ' + @BDActual + 
  '    

SELECT @NoColumnas= MAX([N]) FROM  @Columnas 

WHILE (@Ciclos<=@NoColumnas )                             
      BEGIN                              
            SELECT @InsertInto= @InsertInto+''[''+Columna+''],''           
            FROM @Columnas                              
            WHERE [N]=@Ciclos                         

            SELECT      @InnerQuery=@InnerQuery+'' +CASE WHEN [''+Columna+''] IS NULL THEN ''''Null'''' ELSE ''''''''''''''''+                             
                        REPLACE(CONVERT(VARCHAR(MAX),RTRIM([''+Columna+''])) ,'''''''''''''''',''''''''  )                             
                        +'''''''''''''''' END+' + '''+'''''',''''''' + 
  '                              
            FROM @Columnas                             
            WHERE [N]=@Ciclos                             

            SET @Ciclos=@Ciclos+1                             
      END    
                            
SELECT @InnerQuery=LEFT(@InnerQuery,LEN(@InnerQuery)-4)             
SELECT @InsertInto= SUBSTRING(@InsertInto,0, LEN(@InsertInto))   
SELECT @InnerQuery='' SELECT  ''+''''''INSERT INTO ' + @BaseDeDatos + '.' + @Esquema + '.' + @Tabla + '''+''(''+@InsertInto+'')''
                  +'' VALUES ( ''+'''''''' + ''+''+@InnerQuery+''+''+ '''''')'''' AS [BKINSERTS]''
                  +'' FROM  ' + @BaseDeDatos + '.' + @Esquema + '.' + @Tabla + '(NOLOCK) ''
                  +'' WHERE 1=1 ' + @Where + '''' + CHAR(13) + + ' EXEC (@InnerQuery) '

EXEC (@Query)

SET NOCOUNT OFF

En su versión fuente de Neeraj Prasad Sharma los parámetros de tabla y el condicionante no están divididos y se pasa todo como una consulta general omitiendo el "SELECT * FROM" -por decirlo así -, es decir, a modo de ejemplo un parámetro valido seria: 

'dbo.DimAccount WHERE AccountCodeAlternateKey>1100'

Ahora, ¿Por que no lo deje así?. Bueno, por un lado fue una cuestión de orden y por otro, una de control al separar las secciones de las consultas en Base de Datos, Esquema, Tabla y Where, me resulta mas sencillo por ejemplo, generar tablas con esos datos y someter el procesos en un ciclo WHILE para generar varios INSERTS de manera automática si en dado caso así lo requiero. Por ejemplo: 

DECLARE @INSERTSTABLE TABLE
([N] SMALLINT IDENTITY, 
 BD VARCHAR(50,
 Esquema CHAR(3),
 Tabla VARCHAR(100),
 [Where] VARCHAR(MAX))

INSERT INTO @INSERTSTABLE VALUES('AdventureWorksDW2008R2','dbo','DimAccount',' AND AccountCodeAlternateKey>1100 ')
INSERT INTO @INSERTSTABLE VALUES('AdventureWorksDW2008R2','dbo','DatabaseLog',' AND object=''''DimProduct'''' ')
INSERT INTO @INSERTSTABLE VALUES('Lab','dbo','Test',NULL)
INSERT INTO @INSERTSTABLE VALUES('Lab','dbo','TrashData',' AND Value=''''TRASH'''' ')

WHILE (@Ciclos<=(SELECT MAX([N]) FROM @INSERTSTABLE))                             
       BEGIN 
             <PASAR PARAMETROS TOMADOS DE LA POSICION DE LA TABLA @INSERTSTABLE>                            
             <GENERA INSERTS> o <GENERA INSERTS DINAMICO
             SET @Ciclos=@Ciclos+1                             
       END     

Esto claro en caso de que no puedas crear un procedimiento almacenado como tal... en cuyo caso se vería mas elegante. Aquí solo hay que tener cuidado en lo que se pasa como valor para  WHERE en donde en caso de que se filtren datos usando un campo CHAR, VARCHAR o similares debe de cumplir con cierto formato. Si, por otro lado se te permite trabajar sin problemas con tablas temporales, quizá estos query resulten innecesarios y muy rebuscados. Creo que aun puede pulirse un par de detalles que espero ir trabajando mientras le vaya dando uso. Entre tanto, espero que a alguien le resulte útil. Comentarios sobre mejoras o detalles se reciben con gusto. 

Saludos.

REFERENCIAS:


No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.