Split sql sans table temporaire

Soyez le premier à donner votre avis sur cette source.

Vue 11 796 fois - Téléchargée 597 fois

Description

Voici une fonction qui permet de splitter les données contenues dans une seule colonne avec séparateur, sans passer par une table temporaire.

Source / Exemple :


// Code source complet dans le zip !

public string SqlSplitSeparateur(ArrayList champsList, string separateur, int idxFirstCol)
{
    string currentIndex = idxFirstCol.ToString(); // index de la 1ère colonne dans le fichier
    string subStrPrec = "";
    string colLen;
    string sqlSelect = "";

    int i = 0;
    foreach (Champ champ in champsList)
    {
        i++;
        if (i == 1)
        {
            // Pour la 1ère ligne, aller jusqu'à la 1ere occurence du séparateur
            subStrPrec = "SUBSTRING(" +
                TMP_COL + "," +
                idxFirstCol + "," +
                "CHARINDEX('" + separateur + "'," + TMP_COL + "," + idxFirstCol + ")-" + idxFirstCol + ")";
        }
        else
        {
            // Lignes suivantes : calcul de la longueur de la colonne (de currentIndex au prochain séparateur / fin)
            if (i < champsList.Count)
                colLen = "(CHARINDEX('" + separateur + "'," + TMP_COL + "," + currentIndex + ")" + "-(" + currentIndex + "))";
            else
                colLen = "999"; // Dernière colonne : aller jusqu'en fin de ligne (il n'y a plus de séparateur !) - NB : on pourrait le calculer : colLen=LEN(TMP_COL)-currentIndex

            subStrPrec = "SUBSTRING(" + TMP_COL + "," + currentIndex + "," + colLen + ")";
        }
        sqlSelect += "RTRIM(LTRIM(" + subStrPrec + ")),";

        // MAJ de la position dans la ligne
        currentIndex += "+LEN(" + subStrPrec + ")+" + separateur.Length;
    }

    sqlSelect = sqlSelect.Remove(sqlSelect.LastIndexOf(','), 1); // On retire la virgule de la fin
    return "SELECT " + sqlSelect + " FROM " + TABLE_TMP;
}

Conclusion :


Voici le principe, qui n'est pas évident à expliquer.

Si on regarde l'instruction SQL générée :

SELECT
RTRIM(LTRIM(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))),
RTRIM(LTRIM(SUBSTRING(COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2,(CHARINDEX(' ; ',COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2)-(1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2))))),
RTRIM(LTRIM(SUBSTRING(COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2+LEN(SUBSTRING(COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2,(CHARINDEX(' ; ',COLUMN_1,1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2)-(1+LEN(SUBSTRING(COLUMN_1,1,CHARINDEX(' ; ',COLUMN_1,1)-1))+2))))+2,999)))
FROM TABLE_SOURCE

On remarque que code qui permet de retourner la 1ère chaîne :

SUBSTRING(COLUMN_1, 1, CHARINDEX(' ; ',COLUMN_1,1)-1)

se retrouve dans le calcul de la recherche du 2ème séparateur. En effet, il suffit de faire un LEN() de cette instruction pour savoir où commencer à chercher la prochaine occurrence du séparateur.
Ainsi de suite, l'instruction qui a permis de trouver la chaîne se retrouve dans le calcul de la chaîne suivante (ce qu'on calcule c'est la position des occurrences du séparateur).

En fait, c'est une requête fractale ^^

Le gros avantage de cette méthode est la rapidité d'exécution, par rapport à une solution classique dans laquelle, à chaque boucle, on sauvegarde les morceaux de chaînes splittées dans une table temporaire, ce qui peut très rapidement générer des milliers d'insert.

La seule limite qui existe est la longueur maximale de la requête générée.

Codes Sources

A voir également

Ajouter un commentaire Commentaires
Messages postés
37
Date d'inscription
lundi 6 octobre 2003
Statut
Membre
Dernière intervention
26 avril 2010

Le lien ci-dessus est dead.

Le code T-SQL est dispo ici :
http://www.sqlfr.com/codes/SQL-SPLIT-SANS-TABLE-TEMPORAIRE_51689.aspx
Messages postés
37
Date d'inscription
lundi 6 octobre 2003
Statut
Membre
Dernière intervention
26 avril 2010

Messages postés
37
Date d'inscription
lundi 6 octobre 2003
Statut
Membre
Dernière intervention
26 avril 2010

Oops, le code ci-dessus n'utilise pas la classe SqlBulkCopy bien sûr, mais juste une connexion OleDb au fichier !
Messages postés
37
Date d'inscription
lundi 6 octobre 2003
Statut
Membre
Dernière intervention
26 avril 2010

Par contre, je n'ai pas poussé les tests très loin à propos du code ci-dessus, mais les perfs en mémoire étaient assez horribles (elle gonfle), et les perfs en terme de temps sont bien moins bonnes...
Messages postés
37
Date d'inscription
lundi 6 octobre 2003
Statut
Membre
Dernière intervention
26 avril 2010

En ajoutant un # devant le nom de la table temporaire, elle devient une "vraie" table temporaire pour SQL Serveur, donc elle reste uniquement en mémoire, et on améliore considérablement les perfs (jai mis à jour le zip). Donc au final, on se retrouve avec le même mode de fonctionnement qu'avec ton code Malkuth (mise en mémoire des lignes, nettoyage, écriture des lignes propres).

@Malkuth : J'ai testé la classe SqlBulkCopy en utilisant une connexion OleDb au fichier. L'implémentation est bien plus simple. En gros, ça donne :

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(mySqlConnection, SqlBulkCopyOptions.TableLock, mySqlTransaction);
sqlBulkCopy.DestinationTableName = "TABLE_FINALE";
sqlBulkCopy.BulkCopyTimeout = 10*60;

string folder = @"D:\test";
string connectionStringTextFile = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + folder + ";Extended Properties='Text;'";
System.Data.OleDb.OleDbConnection connectionTextFile = new System.Data.OleDb.OleDbConnection(connectionStringTextFile);
connectionTextFile.Open();
string fileName = "fichier.txt"; // Attention, toutes les extensions ne sont pas prises en compte !
int nbLignesHeader = 3;
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + fileName, connectionTextFile);
DataSet dsFile = new DataSet(fileName);
da.Fill(dsFile, fileName);
DataTable dtFile = dsFile.Tables[fileName];

int idxLigne=0;
foreach (DataRow row in dtFile.Rows)
{
// Ignorer les lignes de Header
if (idxLigne++ < nbLignesHeader) continue;

ligne = row[0].ToString(); // ligne entière

// Splitter la ligne
tabLigne = ligne.Split(';');

for (int i=0; i<tabLigne.Length; i++)
{
valeur = tabLigne[i];
valeur = valeur.ToString().Trim();
rowOut[i] = valeur;
}
dtOut.Rows.Add(rowOut);

if (idxLigne % 10000 == 0)
{
sqlBulkCopy.WriteToServer(dtOut);
dtOut.Rows.Clear();
}
}
if (dtOut.Rows.Count > 0)
{
sqlBulkCopy.WriteToServer(dtOut);
dtOut.Rows.Clear();
}
connectionTextFile.Close();

-> Maintenant, si on imagine une procédure stockée classique qui splitte les données en utilisant une vraie table temporaire, est-on encore gagnant avec ma méthode ?
Afficher les 23 commentaires

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.