J'ai une table avec 20000 lignes, et une colonne qui a des strings séparées par des virgules. Je veux séparer chacune des strings séparées par des virgules de chaque ligne et les insert dans une table temporaire rangée par rangée sans aucun doublon. Je veux créer une procédure stockée pour cette table avec le nom de la table et la colonne en tant que parameters de cette procédure stockée.
Voici la table
CREATE TABLE [dbo].[Campaign] ( [ID] [int] IDENTITY(1,1) NOT NULL, [MISC] [nvarchar](4000) NULL, PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Voici datatables qui ont
insert into Campaign(MISC) values ('h101,h202,h506,h707,h112,h566'), ('h101,h102,h508,h113,h456,h678'), ('h110,h880,h432,h111,h102,h509,h213'), ('h456,h609,h432,h456,h678,h101,h807')
J'ai un login pour une seule string mais je ne sais pas comment gérer ces 20000 lignes de strings séparées par des virgules dans la colonne donnée – de l'aide?
Une croix Appliquer avec aa Parse devrait faire l'affaire
Option 1 – Avec un FDU
Select A.ID ,B.* From Campaign A Cross Apply [dbo].[udf-Str-Parse-8K](A.Misc,',') B
Option 2 – Sans UDF
Select A.ID ,B.* From Campaign A Cross Apply ( Select RetSeq = Row_Number() over (Order By (Select null)) ,RetVal = LTrim(RTrim(Bivalue('(./text())[1]', 'varchar(max)'))) From (Select x = Cast('<x>' + replace((Select replace(A.Misc,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A Cross Apply x.nodes('x') AS B(i) ) B
Les deux returns
ID RetSeq RetVal 1 1 h101 1 2 h202 1 3 h506 1 4 h707 1 5 h112 1 6 h566 2 1 h101 2 2 h102 2 3 h508 ... 4 3 h432 4 4 h456 4 5 h678 4 6 h101 4 7 h807
Le file UDF si nécessaire
CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@Ssortingng varchar(max),@Delimiter varchar(25)) Returns Table As Return ( with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), cte2(N) As (Select Top (IsNull(DataLength(@Ssortingng),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ), cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Subssortingng(@Ssortingng,tN,DataLength(@Delimiter)) = @Delimiter), cte4(N,L) As (Select SN,IsNull(NullIf(CharIndex(@Delimiter,@Ssortingng,sN),0)-SN,8000) From cte3 S) Select RetSeq = Row_Number() over (Order By AN) ,RetVal = LTrim(RTrim(Subssortingng(@Ssortingng, AN, AL))) From cte4 A ); --Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/ --Much faster than str-Parse, but limited to 8K --Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',') --Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||') --Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)