0

I need to create distinct records by taking multiple values at once.

I have two textbox inputs on my page. First one is for "group number" and second one is for "Items". Group number text value is assigned to @GroupNo parameter.

I'm taking 1 or more values from "Items" multiline textbox, replacing "\r\n" delimiter with ",". This string is assigned to @No parameter.

My pseudocode for rest is as follow;

    Let a,b,c,d be our items, a.k.a the @No parameter.

        (LEN(@No)-LEN(REPLACE(@No,',','')))+1=@x 
    //For determining how many records are in the @No string ı'm counting the number of 
       commas and adding 1 to the result. 
    //@X represents the count of records from input.

        DECLARE @cnt INT = 0;
        WHILE @cnt<@x
        BEGIN
        --->Insert Statement<----
        END

Right now ,I'm stuck at how to create the Insert Statement.

Inserting multiple rows in a single SQL query? has shown me something I wasn't aware before but still I have no idea how to insert all distinct records. GroupNo for each record will be same since they belong to same group but rest is just chaos for me.

Also I'm very open to any improvements on my code or approach.

Edit: Using a table valued parameter helped a lot. I managed to do what I want with this 

INSERT INTO Z (GroupNo,No)
SELECT 
@GroupNo,
Item FROM fn_SplitString('12345678,23456781,345678123,456781234',',')
Community
  • 1
  • 1
Ege Bayrak
  • 1,139
  • 3
  • 20
  • 49
  • 2
    I would change your approach to use [table valued parameters](https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx), and do the split on the application layer. I'd suggest first reading - [Split strings the right way – or the next best way](http://sqlperformance.com/2012/07/t-sql-queries/split-strings), [Splitting Strings : A Follow-Up](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up), and [Splitting Strings : Now with less T-SQL](http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql) – GarethD Jun 03 '15 at 11:31
  • Thanks for the links and the advice, I was thinking about using a table valued parameter myself too. – Ege Bayrak Jun 03 '15 at 11:34
  • Please consider re-posting your solution as an answer. This is a Questions and *Answers* site, after all. :) – Andriy M Jun 04 '15 at 12:36

0 Answers0