Home

Importar varias imágenes a SQL Server 2008 R2 utilizando T-SQL

webmaster's picture

Hace unos dias nos encontramos con la tarea de importar datos procedentes de ficheros csv junto con sus imágenes de forma masiva a una tabla de base de datos. Concretamente se trataba de todos los países del mundo con la información sobre su nomenclatura ISO2 e ISO3, los prefijos telefónicos y la norma CCTLD para las extensiones de páginas Web y me pareció interesante incorporar esa información para que estuviera disponible a todas nuestras aplicaciones. Más adelante en otro artículo aprovechamos parte de los datos para incorporarlos a Mycrosoft Dynamics CRM 2011.

En el file paises.rar que adjuntamos en este artículo se encuentran todas las fuentes utilizadas en este ejercicio.

 

 

Primeramente creamos la bases de datos PAISES y dentro de ella la tabla TBL_PAISES con la siguiente estructura:

[COD_PAIS] [int] NOT NULL, (Indice principal de la tabla)
[ISO2_PAIS] [nvarchar](2) NULL,  (Campo para la siglas del pais con 2 caracteres)
[NOMBRECORTO_PAIS] [nvarchar](150) NULL,  (nombre corto del Pais)
[NOMBRELARGO_PAIS] [nvarchar](255) NULL,  (nombre largo del Pais)
[ISO3_PAIS] [nvarchar](5) NULL, (Campo para la siglas del pais con 3 o más caracteres)
[CODIGO_PAIS] [nvarchar](5) NULL, (Codigo internacional del Pais)
[ONU_PAIS] [bit] NULL,  (Si pertenece a la ONU o no)
[PREFIJOTEL_PAIS] [nvarchar](10) NULL,  (Prefijo telefónico del PAIS)
[CCTLD_PAIS] [nvarchar](5) NULL,  (Terminación de internet según el País)
[FLAG_PAIS] [varbinary](max) NULL, (Campo para almacenar la imagen de la bandera)
[FILE_PAIS] [nvarchar](10) NULL,  (Nombre del fichero de la bandera del pais)

Como los datos que vamos a importar provienen de Fichero csv los hemos maquetado con las correspondientes instrucciones T-SQL para realizar la carga en la tabla diseñada; exepto los campos [FLAG_PAIS] y [FILE_PAIS] que los trataremos más adelante. Mostramos a continuación como sería las lineas para los dos primeros registros. En el fichero "insert_country_code_us.sql" adjuntamos todas las lineas.

 

USE PAISES
INSERT INTO TBL_PAISES (COD_PAIS, ISO2_PAIS, NOMBRECORTO_PAIS, NOMBRELARGO_PAIS, ISO3_PAIS, CODIGO_PAIS, ONU_PAIS, PREFIJOTEL_PAIS, CCTLD_PAIS, FLAG_PAIS, FILE_PAIS) 
VALUES (1, 'AF', 'Afghanistan', 'Islamic Republic of Afghanistan', 'AFG', '004', 1, '93', '.af', NULL, NULL),
(2, 'AX', 'Aland Islands', 'Aring;land Islands', 'ALA', '248', 0, '358', '.ax', NULL, NULL)
 

En otro fichero *.csv teniamos los nombres de los países en Español, por lo que agregamos las correspondientes sentencias para poder añadir la información a un campo nuevo [NOMBRESP_PAIS] según el campo [ISO2_PAIS]. En el fichero "update_country_codes_sp.sql" adjuntamos todas las lineas.

Ya por ultimo nos quedaba agregar las imágenes de las banderas de los países que teniamos en una carpeta. Las imágenes las podeis obtener en el fichero que adjunto llamado "country-flags.zip". En nuestro artículo "Save Image to Database Table in SQL Server 2005" ya habiamos probado algo parecido pero con una sola imágen utilizando la función OPENROWSET. En este caso se me ocurrió leer el contenido de la carpeta (filename.extension) y agregarlo en una tabla temporal y posteriormente programar un procedimiento almacenado que fuera recorriendo la tabla temporal y segun el nombre del fichero (sin la extensión) busar su equivalente en el campo [ISO2_PAIS] de la  [TBL_PAISES] y para posteriores ocasiones almacenar el nombre del file en el campo [FILE_PAIS]

Para esto seguimos el siguiente procedimiento:

1. Creamos la Tabla Temporal

USE PAISES
CREATE TABLE TBL_IMAGELIST 
IMG_FILENAME NVARCHAR(10)) 
GO 

2. Utilizamos el comando de la shell de SQL SERVER para direccionar la salida al campo [IMG_FILENAME]

 DECLARE @SQL AS NVARCHAR(2000) 
  SET @SQL = N'xp_cmdshell ''dir e:\software\country-flags /B''' INSERT INTO TBL_IMAGELIST (IMG_FILENAME) 
  EXEC sp_executesql @SQL
GO

3. Puede ser que por motivos de lectura de la carpeta se hayan incorporado registros con valores NULL; para lo cual ejecutamos la siguiente sentencia T-SQL

DELETE TBL_IMAGELIST WHERE IMG_FILENAME IS NULL

4. Una vez limpia la tabla ejecutamos el resto de instrucciones para incorporar tanto la imágen de cada registro al campo [FLAG_PAIS ] y el nombre al campo [FILE_PAIS ]

DECLARE @SQL AS NVARCHAR(2000) 
DECLARE @ImgCod AS VARCHAR(10)
DECLARE @ImgFilename AS VARCHAR(10)
USE PAISES
DECLARE filelist CURSOR FOR SELECT IMG_FILENAME  FROM  TBL_IMAGELIST 
OPEN filelist 
FETCH NEXT FROM filelist INTO @ImgFilename WHILE ( @@FETCH_STATUS = 0 ) 
    BEGIN 
   SET @SQL = 'UPDATE TBL_PAISES SET FLAG_PAIS = (SELECT img.bulkcolumn FROM OPENROWSET(BULK     N''e:\software\country-flags\'+@ImgFilename+''', SINGLE_BLOB) AS img), FILE_PAIS = '''+''+@ImgFilename+''' WHERE ISO2_PAIS = LEFT('''+''+@ImgFilename+''', 2)'
    EXEC sp_executesql @SQL
   FETCH NEXT FROM filelist INTO @ImgFilename
END 
CLOSE filelist DEALLOCATE filelist
GO

Ya al final comprobamos que tenemos nuestra TBL_PAISES con todos los datos rellenos y disponible para se utilizada en otros desarrollos.

Fuente: 

Centro Práctico - LEMURSOLUTION

PreviewAttachmentSize
paises.rar10.72 KB
country-flags.zip133.32 KB
Contacto: 
webmaster