Loop and insert more than one comma separated List in SQL

Question

I wish to loop through two comma-separated values and perform an insert

As an example lets consider two variables

Declare   @Qid= 1,4,6,7,8   @Answers = 4,4,3,2,3

set @pos = 0
set @len = 0

WHILE CHARINDEX(',', @Answers, @pos+1)>0
BEGIN
    set @len = CHARINDEX(',', @Answers, @pos+1) - @pos
    set @value = SUBSTRING(@Answers, @pos, @len)

insert into table values(@fdid,@Qid,@fusid, @value)  -- i need Qid also

set @pos = CHARINDEX(',', @Answers, @pos+@len) +1
END

Using this loop I am able to extract @Answers and can perform insert. But I wish to extract @Qid and insert inside the loop.

edit for more clarity it is a feedback module. my result table have Qid and Answer field. Answers are ratings (1 to 5). The values we get in variables @Qid and @Answers are sequential. which means 1st answer will be for 1st question and so on.

edit

as per Shnugo's Answer

Declare @Qid varchar(100)= '1,4,6,7,8',   @Answers varchar(100)= '4,4,3,2,3'
DECLARE @tbl TABLE(ID INT IDENTITY, Questions VARCHAR(100),Answers VARCHAR(100));
INSERT INTO @tbl VALUES(@Qid,@Answers)


 INSERT INTO table(FeedbackId,QuestionId,FeedbackUserId,Answer)
 SELECT  1,
  A.qXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS QuestionNumber,3
      ,A.aXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS AnwerNumber
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE(@Qid,',','</x><x>') + '</x>' AS XML)
                  ,CAST('<x>' + REPLACE(@Answers,',','</x><x>') + '</x>' AS XML)) A(qXml,aXml)
CROSS APPLY(SELECT TOP(A.qXml.value('count(/x)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) B(QuestionCount)

Alternatively use `string_split`, all supported sql-server versions — Serg, Dec 05 '19 at 08:29
And are these the pairs, that you want to insert: `(1, 4), (4, 4), (6, 3), (7, 3), (8, 3)`? — Zhorov, Dec 05 '19 at 08:36
@Serg, The developers forgot to include the fragments position into the result set. The documentation states explicitly, that the set might not be returned in the expected order... — Shnugo, Dec 05 '19 at 08:48
my table have questionid and answer fields. and the values are sequential which means 1st answer ill be for 1st question and so on. — Surensiveaya, Dec 05 '19 at 08:56
@SachuMine And most important: Your problem is something else actually: It's the table's design. Such values should not be stored in CSV strings but in related side tables... — Shnugo, Dec 05 '19 at 09:00
i use sql server 2014. And i am not keeping the values as CSV. questionid and answer are two individual fields — Surensiveaya, Dec 05 '19 at 09:02
You shouldn't be messing around with comma delimited strings in SQL Server to begin with. If you are getting this data from a client application that supports table valued parameters, use them. — Zohar Peled, Dec 05 '19 at 09:09

Zhorov · Answer 1 · 2022-08-10T06:12:49.897

3

If you use SQL Server 2016 or higher, you may try to use the next JSON-based approach to map questions and answers by their positions in the input strings. You need to transform the input strings into valid JSON arrays and then use OPENJSON() with default schema to parse the arrays. The result is a table, with columns key, value and type and the key column holds the index of the element in the specified array.

Note, that STRING_SPLIT() function does not guarantee the order of the rows and the output rows might be in any order.

Statement:

DECLARE @Qid nvarchar(max) = N'1,4,6,7,8'
DECLARE @Answers nvarchar(max) = N'4,4,3,2,3'

-- Build your INSERT statement as you expect
-- INSERT INTO Table ...
SELECT j1.[value] AS Qid, j2.[value] AS Answers  
FROM OPENJSON(CONCAT(N'[', @Qid, N']')) j1
JOIN OPENJSON(CONCAT(N'[', @Answers, N']')) j2 ON j1.[key] = j2.[key]

Result from the SELECT statement:

Qid Answers
1   4
4   4
6   3
7   2
8   3

edited Aug 10 '22 at 06:12

answered Dec 05 '19 at 08:48

Zhorov

28,486
6
27
52

could you also specify the answer if we use SQL Server 2014 version – Surensiveaya Dec 05 '19 at 09:09
1

@SachuMine For versions before 2016, you need to use a XML-based approach, like in the Shnugo's answer. Thanks. – Zhorov Dec 05 '19 at 09:16
1

@SachuMine That is, why stating the version is so important. This great answer gets somehow invalidated... However, I just placed a position-safe XML-based answer which works on SQL-Server 2014... – Shnugo Dec 05 '19 at 09:16
Sorry for not specifying the version – Surensiveaya Dec 05 '19 at 09:20

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

I'd prefer Zhorov's JSON answer (needs v2016+).

If you use a SQL-Server below 2016 you might use this position-safe XML-based solution:

A mockup table to simulate your issue with two different rows.

DECLARE @tbl TABLE(ID INT IDENTITY, Questions VARCHAR(100),Answers VARCHAR(100));
INSERT INTO @tbl VALUES('1,4,6,7,8','4,4,3,2,3')
                      ,('1,2,3','4,5,6');

--The query

SELECT t.*
      ,A.qXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS QuestionNumber
      ,A.aXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS AnwerNumber
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE(t.Questions,',','</x><x>') + '</x>' AS XML)
                  ,CAST('<x>' + REPLACE(t.Answers,',','</x><x>') + '</x>' AS XML)) A(qXml,aXml)
CROSS APPLY(SELECT TOP(A.qXml.value('count(/x)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) B(QuestionCount);

The idea in short:

We need a CROSS APPLY and some string methods to transform something like 1,2,3 to an xml like <x>1</x><x>2</x><x>3</x>.
Now we can use value() with XQuery count() to find the actual count of questions.
We need one more CROSS APPLY with a computed TOP() clause to get a set of running number from 1 to n with n=countOfQuestions. I do this against master..spt_values. This is just a well-filled standard table... We do not need the values, just any set to create the counter...
Finally we can use .value() in connection with sql:column() in order to fetch the question and the corresponding answer by their positions.

UPDATE: Non-tabular data

If you do not get these CSV parameters as a table you can use this:

Declare @Qid varchar(100)= '1,4,6,7,8',   @Answers varchar(100)= '4,4,3,2,3'

--INSERT INTO table(FeedbackId,QuestionId,FeedbackUserId,Answer)
SELECT  1
       ,A.qXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS QuestionNumber
       ,3
       ,A.aXml.value('(/x[sql:column("B.QuestionCount")])[1]','int') AS AnwerNumber
FROM (SELECT CAST('<x>' + REPLACE(@Qid,',','</x><x>') + '</x>' AS XML)
            ,CAST('<x>' + REPLACE(@Answers,',','</x><x>') + '</x>' AS XML)) A(qXml,aXml)
CROSS APPLY(SELECT TOP(A.qXml.value('count(/x)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values) B(QuestionCount);

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 05 '19 at 09:12

Shnugo

66,100
9
53
114

@SachuMine, regrettfully the developers of SQL Server did not really implement a lot of string funtionality. CSV data (a comma delimited list of numbers in your case) is something they eventually did not even want to support. But this is not really complex... The statement will return a list, which you can insert to any table directly without any loop. Easiest was `INTO dbo.NewTargetTable` right before the `FROM`. You can than proceed with the newly created table... – Shnugo Dec 05 '19 at 09:30
Thanks for the answer. But it seems to be little complex for me to understand. My question was really to perform an insert inside a loop (or any other way) like ----insert into table values(fdid,Qid,fusid, Ans) --- here fdid and fusid are some fixed values and Qid anf Ans are as extracted in order from the comma separated value – Surensiveaya Dec 05 '19 at 09:36
1

@SachuMine Might be, that you just think to complex... You can easily use `INSERT INTO YourTable([list of target columns here]) SELECT [list of columns here] FROM...` and use my `SELECT` as above. No loops, no `VALUES()`... – Shnugo Dec 05 '19 at 09:50
i am really sorry, i am still hanging around. Lets say my table have 5 fields.create table tab(id int identity,FeedbackId int,QuestionId int,FeedbackUserId int,Answer int) -- i wish to create a procedure inside of that we write insert statement. as parameters we get values for FeedbackId (lets say 22) and FeedbackUserId (lets say 34). And values for QuestionId = 1,4,6,7,8 and Answer = 4,4,3,2,3 are comma seperated. could you help me with an insert query. – Surensiveaya Dec 05 '19 at 10:06
Could you check my edit in Question and comment whether its correct. Do we need to create Temp Table always – Surensiveaya Dec 05 '19 at 10:19
@SachuMine In your edit you provide a list of four columns in the target list (`FeedbackId,QuestionId,FeedbackUserId,Answer`). Two of these values you provide hard-coded in the `SELECT`... I cannot know, what you are trying to achieve... In most cases it is a god idea to use a staging table in between (not really needed, but smart). you might use `SELECT ... INTO #StagingTable FROM...` and then proceed with `#StagingTable`... – Shnugo Dec 05 '19 at 10:41
that hardcoded values are for testing. Actualy as i said those values we will get as a parameter value. Actually i am getting the desired output as per your answer and the edit i have provided in question. my question is do we neeed to create the @tbl always – Surensiveaya Dec 05 '19 at 10:50
Perfect. Thank you so much. Really appreciate your efforts – Surensiveaya Dec 05 '19 at 11:01

Ajay2707 · Answer 3 · 2019-12-05T09:12:39.017

0

You have not described relationship of question and its answers. I feel its one to one relationship and for that, I have given the answer.

declare @Qid varchar(200)= '1,4,6,7,8' , @Answers varchar(200) = '4,4,3,2,3'

;with cte
as(
    select id, data qid from dbo.Split (@qid, ',') 
),
cte1 as
(
select id, data ansid from dbo.Split (@answers, ',') 
)


--insert into tablename 
select
qid, ansid from cte join cte1 on cte.id = cte1.id

Result will be:

See other option for later version of sqlserver : Split function equivalent in T-SQL?

edited Dec 05 '19 at 09:12

answered Dec 05 '19 at 08:49

Ajay2707

5,690
6
40
58

The `dbo.Split()` function you call is not a standard function... This answer won't help... At least not more than a general idea about a possible approach... – Shnugo Dec 05 '19 at 08:57
@Shnugo, I do not know, but I usually used this from long time in 2016 version. Thanks. For sachuMine, you can use STRING_SPLIT() instead of split function. – Ajay2707 Dec 05 '19 at 09:09
Now, not again `STRING_SPLIT()`! Please read through the comments and the other answers. `STRING_SPLIT()` does not guarantee to return in the expected order. This is absolutely the wrong choice to bind sets by position... – Shnugo Dec 05 '19 at 09:14

Loop and insert more than one comma separated List in SQL

3 Answers3

UPDATE: Non-tabular data