How to separate variables and values, then insert in a table?

Question

Problem

A stored procedure is receiving list of variables and values, and the delimiter. This stored procedure needs to insert those in a table.

--Example table
create table #tempo
(
    Variable1 int,
    Variable2 int,
    Variable3 int
)

These are the parameters to the stored procedure:

declare @variableList varchar(100)
declare @valueList varchar(100)
declare @separator char(1)

set @variableList = 'Variable1#Variable2#Variable3'
set @valueList = '1111#2222#3333'
set @separator = '#'

Result

What I want to achieve is this:

select * from #tempo

+---------+---------+---------+
|Variable1|Variable2|Variable3|
+---------+---------+---------+
|1111     |2222     |3333     |
+---------+---------+---------+

One way to do it

I can use a loop and build dynamic SQL but I want to avoid it. Other than the obvious reasons for not using dynamic SQL, the loop structure is hard to maintain, explain and testing can become an issue too.

Ideal way

I am thinking about a more elegant way to do this, for example with string_split or coalesce etc. But cannot figure out a way without using dynamic SQL or loops.

Dynamic SQL is your only choice. This is because no T-SQL statement except a dynamic one can produce a result set with a variable number of columns. (Excluding cheats like remoting queries.) T-SQL *really* likes static result sets. This does *not* mean you need cursors, though -- building a statement dynamically can be done with things like `STRING_AGG` and other set constructs. — Jeroen Mostert, Feb 01 '19 at 15:39
Are the number of variables fixed (3 in your example) or dynamic? — iMajek, Feb 01 '19 at 15:40
If your stored procedure will always handle the same columns and those column names are known in advance, then it is possible to avoid dynamic sql with a lot of conditional logic and loops. However, IMO, the dynamic sql solution will actually be MORE elegant, not less. — Tab Alleman, Feb 01 '19 at 15:46
@JeroenMostert Unfortunately, SQL is 2016, not 2017. I edited the tag. Sorry for the confusion. — Farhan, Feb 01 '19 at 15:47
I don't understand why you need a stored procedure to receive the column names and the values and does nothing put spit it back out. This has all the tell tale code smell of an [xy problem](http://xyproblem.info/) — Sean Lange, Feb 01 '19 at 15:52
`SELECT 'INSERT #tempo(' + @variableList + ') VALUES (' + @valueList + ')';`, then `EXEC` that? Obviously too simple if the number of columns is dynamic, but you said it wasn't. (Will also not work for values more complicated than `INT`, since you'd need to escape things like strings properly. Also a potential SQL injection risk, yada yada, I'm assuming this input is vetted.) Per Sean, though, this does seem like the kind of thing that client code should be solving, and not something a stored procedure should be doing. Even if all must happen in the DB itself, there's always SSIS. — Jeroen Mostert, Feb 01 '19 at 15:54
@JeroenMostert and a REPLACE would be needed if the values were separated by a character other than a comma. — Tab Alleman, Feb 01 '19 at 15:55
@TabAlleman: yeah, I don't really see the appeal of a customizable delimiter here anyway, so I semi-deliberately glossed over it. :-) — Jeroen Mostert, Feb 01 '19 at 15:56
@SeanLange I don't need a stored procedure but I inherited it. — Farhan, Feb 01 '19 at 16:09
But do you see the absurdity of assembling a delimited list and just sending it back. Would be a lot easier to just generate the table and leave sql out of the picture. — Sean Lange, Feb 01 '19 at 16:11
@JeroenMostert There are many things in life you don't have control over. This is one of those. :( — Farhan, Feb 01 '19 at 16:11
@SeanLange I agree about somethings being absurd. However, there can be other delimiters being sent (e.g. # instead of a comma). — Farhan, Feb 01 '19 at 17:07
Not sure that makes much difference. You are basically building the result set and sending to your procedure as delimited strings so the procedure can parse those delimited strings and pass back what could have been built already. But I understand you inherited this and have to make it work. — Sean Lange, Feb 01 '19 at 17:16

score 1 · Answer 1 · answered Feb 01 '19 at 15:57

1

If you always have same set of column names then it is very easy to do with pivoting, but if columns are changing then you can use the same script with dynamically adjusted list of variables, provided as a parameter or from direct reading from temp table:

INSERT INTO #tempo SELECT *
FROM (
    SELECT [value], rv = 'Variable' + CAST(Row_Number() OVER ( ORDER BY (SELECT 1)) as VARCHAR)
    FROM STRING_SPLIT(@valueList,@separator)
) AS src
PIVOT (MAX([value]) FOR rv IN (Variable1,Variable2,Variable3)) AS pvt;

answered Feb 01 '19 at 15:57

Slava Murygin

1,951
1
10
10

I like this solution, only one issue. If the column names are different (not Variable1,Variable2,Variable3), then it can cause an issue. – Farhan Feb 01 '19 at 16:08
Exactly. In that case you have to replace that list using dynamic SQL. – Slava Murygin Feb 01 '19 at 16:13

score 1 · Answer 2 · answered Feb 01 '19 at 15:58

You can always try pivoting out the data. This is just the select, but could easily have an insert wrapped into it.

We use a split string with a row ID to allow matching of two split data sets. Function is :

CREATE FUNCTION [dbo].[Split] (@RowData NVARCHAR(MAX), @SplitOn NVARCHAR(5))
    RETURNS @RtnValue TABLE (Id INT IDENTITY(1, 1), Data NVARCHAR(100))
    AS
        BEGIN
            DECLARE @Cnt INT;

            SET @Cnt = 1;

            WHILE (CHARINDEX(@SplitOn, @RowData) > 0)
                BEGIN
                    INSERT INTO @RtnValue (Data)
                    SELECT  Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, CHARINDEX(@SplitOn, @RowData) - 1)));

                    SET @RowData = SUBSTRING(@RowData, CHARINDEX(@SplitOn, @RowData) + 1, LEN(@RowData));
                    SET @Cnt = @Cnt + 1;
                END;

            INSERT INTO @RtnValue (Data)
            SELECT  Data = LTRIM(RTRIM(@RowData));

            RETURN;
        END;

You can then join the two sets together to give some key value pairs, and from there pivot out the data to give the format you requested. If you replace the last select with a select from any of the previous cte's then you can see how the logic unfolds.

DECLARE @variableList VARCHAR(100);
DECLARE @valueList VARCHAR(100);
DECLARE @separator CHAR(1);

SET @variableList = 'Variable1,Variable2,Variable3';
SET @valueList = '1111, 2222, 3333';
SET @separator = ',';

WITH cteVar AS (SELECT  Id, Data FROM   dbo.Split(@variableList, @separator) )
,    cteVal AS (SELECT  Id, Data FROM   dbo.Split(@valueList, @separator) )
,    cteData AS
    (SELECT cteVar.Data VariableData
     ,      cteVal.Data ValueData
       FROM cteVar
       JOIN cteVal ON cteVal.Id = cteVar.Id)
,    ctePivot AS
    (SELECT *
       FROM cteData
         PIVOT (   MAX(ValueData)
                   FOR VariableData IN ([Variable1], [Variable2], [Variable3])) AS PivotTable)
SELECT  *
  FROM  ctePivot;

This is quite a long approach to it but hopefully it well help you understand the steps involved. Its worth looking at the Pivot function in general anyway, its well documented.

There is no need to use custom function "STRING_SPLIT" is available in 2016 — Slava Murygin, Feb 01 '19 at 16:04
using loops to split strings is the worst possible way to do this. There are plenty of set based approaches. https://sqlperformance.com/2012/07/t-sql-queries/split-strings and http://www.sqlservercentral.com/articles/Tally+Table/72993/ And on the newer versions there is STRING_SPLIT — Sean Lange, Feb 01 '19 at 16:05
Especially a splitter that uses a `WHILE` loop. If you are going to supply a custom splitter, at least use a dataset approach. — Thom A, Feb 01 '19 at 16:05
Hi guys, STRING_SPLIT wouldn't work in this case, as we are applying an ID as well. STRING_SPLIT only returns data, not an index. We have seen instances where the data doesn't come out in the same order. Your right about efficiency, that was actually our old code, we now use a cte based version, but I was logged into an old box when I pulled. All valid points though. — Matthew Baker, Feb 01 '19 at 16:10
If ordinal position is important, I recommend looking up `delimitedsplit8k_lead`. A `WHILE` is still not the right choice. — Thom A, Feb 01 '19 at 16:13
@Larnu There's a lot in that function - I'll have to give it a try and do some performance testing on it. We don't have to do a lot of string manipulation, but a faster alternative is always welcome. — Matthew Baker, Feb 01 '19 at 16:17

How to separate variables and values, then insert in a table?

2 Answers2