[Snippets] Manipuler un fichier texte d’une colonne comme une table d’une colonne

Publié le par

Voyons comment on peut rendre abstraite la notion de fichier en T-SQL en combinant les commandes OPENROWSET et les vénérables CTE …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
---------------------------------
-- Nicolas SOUQUET - 12/02/2009 -
---------------------------------
WITH
  CTE_IMPORT AS
  (
    SELECT BulkColumn
    FROM OPENROWSET(BULK 'C:\monFichier.txt', SINGLE_CLOB) TMP
  ),
  CTE_INDICES_LIGNES (Deb, Fin) AS  
  (  
      SELECT 1 Deb,
          CHARINDEX(CHAR(10), CTE_IMPORT.BulkColumn + CHAR(10)) Fin
      FROM CTE_IMPORT
    UNION ALL  
      SELECT CAST(CTE_INDICES_LIGNES.Fin + 1 AS INT),
          CHARINDEX(CHAR(10), CTE_IMPORT.BulkColumn + CHAR(10), CTE_INDICES_LIGNES.Fin + 1)
      FROM CTE_IMPORT, CTE_INDICES_LIGNES
      WHERE CHARINDEX(CHAR(10), CTE_IMPORT.BulkColumn + CHAR(10), CTE_INDICES_LIGNES.Fin + 1 ) > 0  
  )
SELECT SUBSTRING(BulkColumn, CTE_INDICES_LIGNES.Deb, CTE_INDICES_LIGNES.Fin - CTE_INDICES_LIGNES.Deb) AS Colonne
FROM CTE_INDICES_LIGNES
CROSS JOIN CTE_IMPORT
WHERE CTE_INDICES_LIGNES.Deb <> CTE_INDICES_LIGNES.Fin

Comme on peut s’y attendre, c’est particulièrement contre-performant :
en activant SET STATISTICS IO puis SET STATISTICS TIME, on obtient successivement, pour un fichier contenant 50 lignes:

Table ‘Worktable’. Nombre d’analyses 3, lectures logiques 810
Temps UC = 40 ms, temps écoulé = 101 ms.

Ce snippet s’avère donc utile pour faire un import d’un faible de nombre de lignes sans passer par une table de base de données, mais il est à éviter pour faire des import massifs.
L’utilisation de la CTE permet de plus de réaliser des jointures …

ElSuket

Laisser un commentaire