Insert incremental value to table

user_3703534 0
Viewed 109

I need to insert a comma-separated string value to the table called productsort. My table structure as follows 

+-------------+-----------+
| fkProductID | SortValue |
+-------------+-----------+
|             |           |
+-------------+-----------+

I have a custom split function which I used to separate values from the comma-separated string

DECLARE @Var VARCHAR(4000);
SET @Var = '188,189,190,191,192,193,194'

-- Insert statement
INSERT INTO productsort(fkProductID)
    SELECT * FROM dbo.fnSplitStringToIds(@Var,',')

Above query works fine and after the insertion table data as follows,

+-------------+-----------+
| fkProductID | SortValue |
+-------------+-----------+
| 188         |           |
| 189         |           |
| 190         |           |
| 191         |           |
| 192         |           |
| 193         |           |
| 194         |           |
+-------------+-----------+

Now what I need to insert an incremental value into SortValue Column as well, I need the following output,

+-------------+-----------+
| fkProductID | SortValue |
+-------------+-----------+
| 188         | 1         |
| 189         | 2         |
| 190         | 3         |
| 191         | 4         |
| 192         | 5         |
| 193         | 6         |
| 194         | 7         |
+-------------+-----------+

How can I do this?

Updated :

Comma-separated string can be like this 193,191,190,189,192,188,194. In such a case, My expected output should be

+-------------+-----------+
| fkProductID | SortValue |
+-------------+-----------+
| 193         | 1         |
+-------------+-----------+
| 191         | 2         |
+-------------+-----------+
| 190         | 3         |
+-------------+-----------+
| 189         | 4         |
+-------------+-----------+
| 192         | 5         |
+-------------+-----------+
| 188         | 6         |
+-------------+-----------+
| 194         | 7         |
+-------------+-----------+
sql-insert csv sql-server-2016 tsql sql-server
2 Answers

You could do an update, using ROW_NUMBER with the fkProductID column providing the ordering of the sequence.

WITH cte AS (
    SELECT fkProductID, ROW_NUMBER() OVER (ORDER BY fkProductID) rn
    FROM productsort
)

UPDATE cte
SET SortValue = rn;
user_18632290

SQL Server 2016, which you are using, introduced table-valued function string_split(). I would recommend using it instead of your custom parser.

On top of that, you can use row_number() in directly your insert statement, so you get the result that you want with a single query:

declare @var varchar(4000);
set @var =  '188,189,190,191,192,193,194'

insert into productsort(fkProductID, sortValue)
select value, row_number() over(order by value) 
from string_split(@var, ',')
user_106767160
Related