CODEWITHSUNDEEP
sql-server-2008csvinsert
user3328730
user3328730

Reputation:

Insert rows into table with single ID and rows from two comma separated lists

I have two comma separated list which I know belong to a single ID, so I need to insert each corresponding value from the two comma separated list into the table with the common ID.

I have @listA, @listB and their values would be

  • @listA ='BRE,CT,DIA,DEXA'
  • @listB ='2.00,3.00,4.00,5.00'

and I know the ID to which these values belong, say

@SpecID=1

Now what I want is to insert data into a table which contains all three columns like this enter image description here

Please provide me the steps to approach this problem.

Upvotes: 0

Views: 44

Answers (2)

Bala
Bala

Reputation: 704

Step 1: I have created the below Function that accepts the Comma separated list items and a Delimiter. It returns the split values along with a RowNumber.

CREATE FUNCTION dbo.SplitString
(
    @Delimiter VARCHAR(1),
    @InputString VARCHAR(MAX)
)
RETURNS
    @ListItems TABLE 
    (
        RowNumber INT,
        List VARCHAR(50)
    )
AS
BEGIN
    DECLARE @Position INT, @RowNumber INT = 1, @ListItem VARCHAR(MAX)

    WHILE CHARINDEX(@Delimiter, @InputString) > 0
        BEGIN
            SELECT @Position  = CHARINDEX(@Delimiter, @InputString)  
            SELECT @ListItem = SUBSTRING(@InputString, 1, @Position-1)

            INSERT INTO @ListItems 
                SELECT @RowNumber,@ListItem

            SELECT @InputString = SUBSTRING(@InputString, @Position+1, LEN(@InputString)-@Position)

            SET @RowNumber = @RowNumber + 1
        END

    INSERT INTO @ListItems 
        Select @RowNumber,@InputString

    RETURN
END

Step 2: Using the above function, I split the comma separated list items and created 2 table variables.

DECLARE @listA VARCHAR(MAX), @listB VARCHAR(MAX), @SpecID INT, @Delimiter VARCHAR(1)

SET @listA= 'BRE,CT,DIA,DEXA'
SET @listB ='2.00,3.00,4.00,5.00'
SET @SpecID = 1
SET @Delimiter = ','

DECLARE @ListItems Table
(
    SpecID INT,
    listA VARCHAR(50),
    listB VARCHAR(50)
)

DECLARE @TableListA Table
(
    RowNumber INT,
    ListA VARCHAR(50)
)

DECLARE @TableListB Table
(
    RowNumber INT,
    ListB VARCHAR(50)
)

INSERT INTO @TableListA
    SELECT * FROM SplitString(@Delimiter,@listA)

INSERT INTO @TableListB
    SELECT * FROM SplitString(@Delimiter,@listB)

INSERT INTO @ListItems 
     SELECT
        @SpecID,
        A.ListA,
        B.ListB
     FROM @TableListA A
     INNER JOIN @TableListB B ON B.RowNumber = A.RowNumber

SELECT * FROM @ListItems

Please use the SQL Fiddle to check the output: http://sqlfiddle.com/#!6/9e12b/1/0

Upvotes: 1

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

You will have to do it like this:

1) Split using `,`
2) Have 2 arrays 
3) Both need to have the same size so iterate over 1 array
4) Use the index within the loop to insert the values.

Upvotes: 0

Related Questions