Le fractionnement délimité par des valeurs dans une colonne SQL en plusieurs lignes

J'aimerais bien quelques conseils ici, pour vous donner quelques informations de fond je suis en train de travailler à l'insertion de journaux de Suivi des Messages à partir d'Exchange 2007 en SQL. Comme nous avons des millions et des millions de lignes par jour, je suis à l'aide de l'instruction Bulk Insert pour insérer les données dans une table SQL.

En fait, j'ai fait Bulk Insert dans une table temporaire, puis à partir de là, j'ai FUSIONNER les données dans la table en direct, c'est pour le test analyse les problèmes que certains domaines par ailleurs avoir des devis et autour de ces valeurs.

Cela fonctionne bien, à l'exception du fait que le destinataire de l'adresse de colonne est un champ délimité, séparées par un ; personnage, et il peut être incroyablement long parfois, comme il peut y avoir beaucoup de destinataires de courrier électronique.

Je voudrais profiter de cette colonne, et de diviser les valeurs sur plusieurs lignes, qui serait ensuite être inséré dans une autre table. Problème est tout ce que je suis en train est soit trop long ou ne fonctionne pas comme je le veux.

Prendre cet exemple de données:

message-id                                              recipient-address
2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com;user4@domain4.com;user5@domain5.com

J'aimerais que ce soit formaté comme suit dans mon tableau des Destinataires:

message-id                                              recipient-address
2D5E558D4B5A3D4F962DA5051EE364BE06CF37A3A5@Server.com   user1@domain1.com
E52F650C53A275488552FFD49F98E9A6BEA1262E@Server.com     user2@domain2.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user3@domain3.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user4@domain4.com
4fd70c47.4d600e0a.0a7b.ffff87e1@Server.com              user5@domain5.com

Quelqu'un a une idée sur comment je peux faire?

Je sais PowerShell, j'ai donc essayé, mais une boucle foreach même sur 28K dossiers a fallu une éternité pour le processus, j'ai besoin de quelque chose qui va courir aussi rapidement et efficacement que possible.

Merci!

Je pense que vous devriez vous mettre trois résultat dans un tableau en utilisant une fonction "split" Regardez ceci : stackoverflow.com/questions/314824/... Et après que vous pouvez gérer à joindre à votre fractionner les données sur vous autre table pour obtenir votre résultat

OriginalL'auteur HungryHippos | 2012-06-13

45

Tout d'abord, créez une fonction split:
```
CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO
```
Maintenant, vous pouvez extrapoler simplement par:
```
SELECT s.[message-id], f.Item
  FROM dbo.SourceData AS s
  CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;
```
Aussi je suggère de ne pas mettre des tirets dans les noms de colonne. Cela signifie que vous devez toujours mettre en [square brackets].

Vous, monsieur, qui méritent une internet cookie 🙂 j'ai dû faire quelques changements, j'ai dû appeler le champ Élément de la Valeur plutôt que PowerShell n'aime pas le nom de l'Élément. J'ai également eu à ajouter 'f' après la CROIX, s'APPLIQUENT à l'alias de la section f de ce nom.f.item/f.valeur travaillé.
Aussi vous entendre sur les noms de colonne, cela a été fait juste pour maintenir la parité avec le journal de suivi des noms de colonnes elles-mêmes, je suis conscient de la nécessité de crochets et c'est bien ok.
brillant exemple. ma déclaration ressemble à ceci: SELECT s.item, f.Élément DE dbconfig COMME s CROSS apply SplitStrings(s).réglage, ';') f OÙ s.item = 'EXE_PATHS'

OriginalL'auteur Aaron Bertrand
3

SQL Server 2016 inclure une nouvelle fonction de table string_split(), similaire à la solution précédente.

La seule exigence est de Définir le niveau de compatibilité de 130 (SQL Server 2016)

OriginalL'auteur Oscar Perez

Vous pouvez utiliser CROIX APPLIQUER (disponible dans SQL Server 2005 et au-dessus) et STRING_SPLIT fonction (disponible dans SQL Server 2016 et au-dessus):

DECLARE @delimiter nvarchar(255) = ';';

-- create tables
CREATE TABLE MessageRecipients (MessageId int, Recipients nvarchar(max));
CREATE TABLE MessageRecipient (MessageId int, Recipient nvarchar(max));

-- insert data
INSERT INTO MessageRecipients VALUES (1, '[email protected]; [email protected]; [email protected]');
INSERT INTO MessageRecipients VALUES (2, '[email protected]; [email protected]');

-- insert into MessageRecipient
INSERT INTO MessageRecipient
SELECT MessageId, ltrim(rtrim(value))
FROM MessageRecipients 
CROSS APPLY STRING_SPLIT(Recipients, @delimiter)

-- output results
SELECT * FROM MessageRecipients;
SELECT * FROM MessageRecipient;

-- delete tables
DROP TABLE MessageRecipients;
DROP TABLE MessageRecipient;

Résultats:

MessageId   Recipients
----------- ----------------------------------------------------
1           user1@domain.com; user2@domain.com; user3@domain.com
2           user@domain1.com; user@domain2.com

MessageId   Recipient
----------- ----------------
1           user1@domain.com
1           user2@domain.com
1           user3@domain.com
2           user@domain1.com
2           user@domain2.com

OriginalL'auteur Alex Vazhev

Vous devez vous connecter pour publier un commentaire.