Read comma separated values and insert in SQL Table in batches

Question

I have 4 columns in a sql table Offer.

id (PK auto increment), name , org , TourCode Varchar(MAX)

TourCode contains comma separated codes e.g (AVG123,JGH12 etc ).

I have 40000 TourCode available as comma separated values

I have to write a query to insert 4000 rows in Offer table with each row containing 10 tour code separated by comma. For all the 4000 rows the value of name and org remains same e.g. "ABC" , "Amazon"

e.g.

DECLARE @TourCodes VARCHAR(4000);
SET @TourCodes =  'AVG123,JGH12,AVasfG123,JGsdfH12,AVsdgG123,JsdgGH12 , A34G123,J56gGH12, A34G1df23,JgfGH12 ,......'

Output:
╦══════════╦════════════╦═══════════════════╗
║ name     ║ org        ║TourCode           ║
╠══════════╬═══════════ ╬═══════════════════╣
║      ABC ║     Amazon ║AVG123,JGH12       ║
║      ABC ║     Amazon ║AVasfG123,JGsdfH12 ║
║      ABC ║     Amazon ║AVsdgG123,JsdgGH12 ║
║      ABC ║     Amazon ║A34G123,J56gGH12   ║
║      ABC ║     Amazon ║A34G1df23,JgfGH12  ║

What should be my best approach.

I am using SQL Server . Thanks in advance.

As of SQL-SERVER 2016 there is the table valued function `string_split()` that could be useful here. — Carsten Massmann, Jun 20 '17 at 04:55
@cars10 string_split() will split the values on the basis of comma and each row will have 1 tourcode and total records are going to be 400.My requirement is , I want 2(sample example) tourcode per row and total row would be 200 — Ravish Kumar, Jun 20 '17 at 05:00

asmgx · Answer 1 · 2017-06-20T05:50:03.387

This is how to split the Comma Separated Values into a table (Taken from How to split a comma-separated value to columns)

CREATE FUNCTION Split (
      @InputString                  VARCHAR(8000),
      @Delimiter                    VARCHAR(50)
)

RETURNS @Items TABLE (
      Item                          VARCHAR(8000)
)

AS
BEGIN
      IF @Delimiter = ' '
      BEGIN
            SET @Delimiter = ','
            SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
      END

      IF (@Delimiter IS NULL OR @Delimiter = '')
            SET @Delimiter = ','

--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic

      DECLARE @Item           VARCHAR(8000)
      DECLARE @ItemList       VARCHAR(8000)
      DECLARE @DelimIndex     INT

      SET @ItemList = @InputString
      SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      WHILE (@DelimIndex != 0)
      BEGIN
            SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
            INSERT INTO @Items VALUES (@Item)

            -- Set @ItemList = @ItemList minus one less item
            SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
            SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
      END -- End WHILE

      IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
      BEGIN
            SET @Item = @ItemList
            INSERT INTO @Items VALUES (@Item)
      END

      -- No delimiters were encountered in @InputString, so just return @InputString
      ELSE INSERT INTO @Items VALUES (@InputString)

      RETURN

END -- End Function
GO

after that

use this code

DECLARE @TourCodes  varchar(4000)
SET @TourCodes  = 'AAAAA,BBBBB,CCCCC,DDDD,EEEE,FFFF , GGG,HHHHH, IIIII,JJJJJ'

DECLARE @T as table (ID int identity, Name varchar(255), Org varchar(255), TourCode varchar(255))

INSERT @T (Name, Org, TourCode)
select 'ABC', 'AMAZON', Item from dbo.split(@TourCodes,',')


Select A.Name, A.Org, ISNULL(A.TourCode, '') + ',' + ISNULL(B.TourCode, '')
from @T A INNER JOIN @T B
ON A.ID = B.ID-1
AND A.ID%2 = 0

This is giving Incorrect result . I want 2 tourcodes per value and it is giving random result — Ravish Kumar, Jun 20 '17 at 05:19
Hey @asmgx ..Thanks for your reply.But this query has a problem .What if I want 10 Tourcode per row.I have to modified the whole query. — Ravish Kumar, Jun 20 '17 at 05:57
in that case you better loop for the comma separated values and after 10 commas change the comma to semicolon then split by semicolon — asmgx, Jun 20 '17 at 06:09

Carsten Massmann · Answer 2 · 2017-06-20T14:00:59.430

What about this solution then?

DECLARE @TourCodes VARCHAR(4000);
SET @TourCodes =  ' AVG123   , JGH12,AVasfG123,JGsdfH12,AVsdgG123,JsdgGH12 , A34G123,J56gGH12, A34G1df23,JgfGH12';

with rcrs AS (
select rtrim(ltrim(LEFT(@TourCodes,charindex(',',@TourCodes)-1))) first,     
       SUBSTRING(@TourCodes+',',charindex(',',@TourCodes)+1,4000) other,
       0 flg
union all
select rtrim(ltrim(LEFT(other,charindex(',',other)-1))) first,
       SUBSTRING(other,charindex(',',other)+1,4000) other ,
       flg+1 flg FROM rcrs WHERE charindex(',',other)>0

) 
SELECT a.first one,b.first two from rcrs a 
INNER JOIN rcrs b ON b.flg=a.flg+1 WHERE a.flg%2=0

In the recursive CTE the string is split starting from the beginning.

It might be noteworthy that I suffixed the original string in the first part of the recursive CTE by an extra ','. This makes sure that all words are "eaten up" by the CTE and can then be processed by the main select using the modulo (%) trick for determining which part goes into column one or two.

The result for this example is:

one       two

AVG123    JGH12
AVasfG123 JGsdfH12
AVsdgG123 JsdgGH12
A34G123   J56gGH12
A34G1df23 JgfGH12

See here for a demo: http://rextester.com/WAA6224

Edit

I obviously had too much time on my hands ;-) ...
and so I went on to try out whether I could actually re-assemble the whole stuff into a ten-column collection the way the OP seems to want it. I am still not completely clear about how the result was meant to look, but here is another shot:

DECLARE @TourCodes VARCHAR(4000);
SET @TourCodes =  REPLACE(REPLACE(
                  ' AVG123   , JGH12,AVasfG123,JGsdfH12,AVsdgG123,JsdgGH12 , A34G123,J56gGH12, A34G1df23,JgfGH12,
                    AVG1234   , JGH126,AVasfG1238,JGsdfH122,AVsdgG1235,JsdgGH127 , A34G1239,J56gGH12a, A34G1df23c,JgfGH12e,
                    AVG1235   , JGH127,AVasfG1239,JGsdfH123,AVsdgG1236,JsdgGH128 , A34G1230,J56gGH12b, A34G1df23d,JgfGH12f',
                   char(10),''),char(13),''); -- this is just a slightly extended sample input string

with rcrs AS (
select rtrim(ltrim(LEFT(@TourCodes,charindex(',',@TourCodes)-1))) first,

       SUBSTRING(@TourCodes+',',charindex(',',@TourCodes)+1,4000) other, 0 flg
union all
select rtrim(ltrim(LEFT(other,charindex(',',other)-1))) first,
       SUBSTRING(other,charindex(',',other)+1,4000) other , flg+1 flg FROM rcrs WHERE charindex(',',other)>0 and flg<30

), cmbn AS (
  SELECT a.flg/20 ii,(a.flg/2)%10 ij ,a.first+','+b.first tc FROM rcrs a
  LEFT JOIN rcrs b ON b.flg=a.flg+1
  WHERE a.flg%2=0
)
SELECT ii,'ABC' name,'Amazon' org, MAX(CASE ij WHEN 0 THEN tc END) tc1,
  MAX(CASE ij WHEN 1 THEN tc END) tc2,
  MAX(CASE ij WHEN 2 THEN tc END) tc3,
  MAX(CASE ij WHEN 3 THEN tc END) tc4,
  MAX(CASE ij WHEN 4 THEN tc END) tc5,
  MAX(CASE ij WHEN 5 THEN tc END) tc6,
  MAX(CASE ij WHEN 6 THEN tc END) tc7,
  MAX(CASE ij WHEN 7 THEN tc END) tc8,
  MAX(CASE ij WHEN 8 THEN tc END) tc9,
  MAX(CASE ij WHEN 9 THEN tc END) tc10
FROM cmbn GROUP BY ii

The result looks like this:

ii name org    tc1            tc2                  tc3                  tc4                tc5                 tc6            tc7                  tc8                  tc9                tc10
0  ABC  Amazon AVG123,JGH12   AVasfG123,JGsdfH12   AVsdgG123,JsdgGH12   A34G123,J56gGH12   A34G1df23,JgfGH12   AVG1234,JGH126 AVasfG1238,JGsdfH122 AVsdgG1235,JsdgGH127 A34G1239,J56gGH12a A34G1df23c,JgfGH12e
1  ABC  Amazon AVG1235,JGH127 AVasfG1239,JGsdfH123 AVsdgG1236,JsdgGH128 A34G1230,J56gGH12b A34G1df23d,JgfGH12f

see here: http://rextester.com/ZJNV34690

Or, if you want all the ten Tourcodes in one columns you could do

SELECT ii,'ABC' name,'Amazon' org, 
           MAX(CASE ij WHEN 0 THEN tc END)+' '+
  COALESCE(MAX(CASE ij WHEN 1 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 2 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 3 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 4 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 5 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 6 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 7 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 8 THEN tc END)+' ','')+
  COALESCE(MAX(CASE ij WHEN 9 THEN tc END)+' ','') tourCodes
FROM cmbn GROUP BY ii

in the main select. See here http://rextester.com/AQCG28977

result:

ii name org     tourCodes
0  ABC  Amazon  AVG123,JGH12 AVasfG123,JGsdfH12 AVsdgG123,JsdgGH12 A34G123,J56gGH12 A34G1df23,JgfGH12 AVG1234,JGH126 AVasfG1238,JGsdfH122 AVsdgG1235,JsdgGH127 A34G1239,J56gGH12a A34G1df23c,JgfGH12e 
1  ABC  Amazon  AVG1235,JGH127 AVasfG1239,JGsdfH123 AVsdgG1236,JsdgGH128 A34G1230,J56gGH12b A34G1df23d,JgfGH12f

Requirement is bit different. I have 40000 TourCode available as comma separated values I have to write a query to insert 4000 rows in Offer table with each row containing 10 tour code .10 tourcode in each row shoudl be separated by comma. — Ravish Kumar, Jun 20 '17 at 06:07
Well, this is not a free coding service. If you can use my snippet to find your own solution than that would be in the best spirit of stackoverflow. Otherwise: keep looking/trying. Good luck! ;-) — Carsten Massmann, Jun 20 '17 at 06:16

asmgx · Answer 3 · 2017-06-20T08:08:09.617

0

Then try this first then try the split SP with a semicolon

this function will seperate every N number of Tours by a semicolon

create FUNCTION GroupBySemicolon (
      @TourCodes                  VARCHAR(8000),
      @HowManyTours               int
)

RETURNS varchar(8000)
AS
BEGIN


DECLARE @i int
DECLARE @Pos int

SET @i = 0
SET @Pos = 1
WHILE @Pos <> 0
BEGIN
    SET @i= @i+1
    SET @Pos = CHARINDEX( ',', @TourCodes,@Pos + 1)
    --SELECT @Pos
    IF @i = @HowManyTours and @Pos > 0
    BEGIN
        SET @i = 0

        SET @TourCodes = LEFT(@TourCodes,@Pos-1) + ';' + RIGHT(@TourCodes, LEN(@TourCodes) - @Pos)
    END

END




RETURN @TourCodes

END

select dbo.GroupBySemicolon( '111,222222,333,44,555555,66 ,7777,8888, 99999,000000', 4)

edited Jun 20 '17 at 08:08

answered Jun 20 '17 at 08:03

asmgx

7,328
15
82
143

Instead of semicolumn , the next set of 4 should come in next row. If there are 12 tourcode and HowManyTours is 4 , then we should have 3 rows of 4 tourcode each !! – Ravish Kumar Jun 20 '17 at 08:40
SELECT 'ABC', 'Amazon', dbo.Split(dbo.GroupBySemicolon( '111,222222,333,44,555555,66 ,7777,8888, 99999,000000', 4), ';') – asmgx Jun 20 '17 at 15:02
if this answers your question, then mark it as ANSWER – asmgx Jun 20 '17 at 15:02

Read comma separated values and insert in SQL Table in batches

3 Answers3