[SQL] 분할 다중 행으로 SQL 열의 값을 구분

분할 다중 행으로 SQL 열의 값을 구분

난 정말 여기에 몇 가지 조언처럼, 내가 SQL에 Exchange 2007에서 메시지 추적 로그를 삽입하는 작업을하고 몇 가지 배경 정보를 제공하는 것입니다. 우리는 내가 SQL 테이블에 데이터를 삽입 할 대량 삽입 문을 사용하고 하루에 수백만 개의 행에 수백만 달러를 가지고.

나는 라이브 테이블에 데이터가 MERGE에서 임시 테이블에 사실 나는 실제로 대량 삽입에서 다음이 특정 필드 그렇지 않으면 값 따옴표 등을 가지고 시험 문제를 분석입니다.

이렇게하면받는 사람 주소의 열이로 구분 럼 식별자 필드가 있다는 사실을 제외하고는 잘 작동한다; 많은 이메일 수신자가있을 수있는 캐릭터, 그리고 긴 때로는 믿을 수 없을 수 있습니다.

이 칼럼을하고 다른 테이블에 삽입 될 여러 행에 값을 분할하고 싶습니다. 문제는 나도 내가 원하는 방식으로 작업이 너무 오래 여부를 취하고 노력하고 무엇이다.

이 예제 데이터를 가지고 :

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

내 수신자 테이블에 다음과 같이이 포맷 할 싶습니다 :

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

아무도 내가이 일에 대해 갈 수있는 방법에 대한 아이디어가 있습니까?

나는이 점에서 시도 그래서 내가 꽤 잘 PowerShell을 알고 있지만, 심지어 28K 기록에 foreach 루프 프로세스에 영원히했다, 나는 가능한 한 효율적으로 빨리 실행됩니다 뭔가를 /이 필요합니다.

감사!

해결법

1. ==============================

   1.첫째, 분할 함수를 만들 :

   첫째, 분할 함수를 만들 :

   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
   

   이제 당신은 단순히으로 추정 할 수 있습니다 :

   SELECT s.[message-id], f.Item
     FROM dbo.SourceData AS s
     CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;
   

   또한 나는 열 이름에 대시를 참을 수 없어하는 것이 좋습니다. 그것은 당신이 항상 [대괄호]에 넣어해야 의미합니다.

2. ==============================

   2.SQL 서버 2016은 이전 솔루션과 유사한 새 테이블 함수 string_split ()를 포함한다.

   SQL 서버 2016은 이전 솔루션과 유사한 새 테이블 함수 string_split ()를 포함한다.

   유일한 요구 사항은로 설정 호환성 수준을 130 (2016 SQL 서버)

3. ==============================

   3.당신은 CROSS이 (SQL 서버 위 2016에서 사용할 수) (SQL 서버 2005 이상 가능) 및 STRING_SPLIT 기능을 적용 사용할 수 있습니다 :

   당신은 CROSS이 (SQL 서버 위 2016에서 사용할 수) (SQL 서버 2005 이상 가능) 및 STRING_SPLIT 기능을 적용 사용할 수 있습니다 :

   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, 'user1@domain.com; user2@domain.com; user3@domain.com');
   INSERT INTO MessageRecipients VALUES (2, 'user@domain1.com; user@domain2.com');
   
   -- 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;
   

   결과 :

   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
   

from https://stackoverflow.com/questions/11018076/splitting-delimited-values-in-a-sql-column-into-multiple-rows by cc-by-sa and MIT license