CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Frenz
Frenz

Reputation: 747

Stored Procedure to Insert comma seperated values as multiple records

Please help me in creating a stored procedure which accepts comma separated values and inserts as multiple rows.

So one parameter @Name will contain values A,B,C and the other parameter @Id will contain values as 1,2,3

The table values after insertion should be as below:

Name     Id  
------------
A         1 
A         2 
A         3 
B         1 
B         2 
B         3 
C         1 
C         2 
C         3

How can I write a stored procedure that can insert the comma-separated values as shown above. Also, If the table already consists of a Name,id pair for example, if A,2 is already there in the table, then it should not insert.

I am using SQL Server 2005. Thanks in advance.

Upvotes: 0

Views: 1596

Answers (3)

Surendra
Surendra

Reputation: 721

You can go and create one user defined functions for splitting the comma separated values into rows as below How this function will work and more on it can be found here 

CREATE FUNCTION dbo.Split
(
 @RowData nvarchar(2000),
 @SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
 Id int identity(1,1),
 Data nvarchar(100)
)
AS
BEGIN
 Declare @Cnt int
 Set @Cnt = 1
 DECLARE @index INT
 SET @index = Charindex(@SplitOn,@RowData)
 While (@index>0)
 Begin
 Insert Into @RtnValue (data)
  Select
 Data = ltrim(rtrim(Substring(@RowData,1,@index-1)))

 Set @RowData = Substring(@RowData,@index+1,len(@RowData))
 Set @Cnt = @Cnt + 1
 SET @index = Charindex(@SplitOn,@RowData)
 End

 Insert Into @RtnValue (data)
 Select Data = ltrim(rtrim(@RowData))

 Return
END

Once this function is created, you can use it for your requirement as below 

declare @Name VARCHAR(30) 
declare @Id VARCHAR(30) 

SET @Name = 'A,B,C'
SET @Id = '1,2,3'

select A.Data,B.Data FROM dbo.Split(@name,',') A ,dbo.Split(@id,',') B

Upvotes: 0

HotblackDesiato
HotblackDesiato

Reputation: 348

Something like this?

DECLARE @var1   VARCHAR(100)='A,B,C';
DECLARE @var2   VARCHAR(100)='1,2,3';

WITH rep1(name, delim) AS
(
    SELECT @var1 name, ',' delim

    UNION ALL

    SELECT LEFT(name, CHARINDEX(delim, name, 1) - 1) name, delim
    FROM rep1
    WHERE (CHARINDEX(delim, name, 1) > 0)

    UNION ALL

    SELECT RIGHT(name, LEN(name) - CHARINDEX(delim, name, 1)) name, delim
    FROM rep1
    WHERE (CHARINDEX(delim, name, 1) > 0)
)
,rep2(id, delim) AS
(
    SELECT @var2 id, ',' delim

    UNION ALL

    SELECT LEFT(id, CHARINDEX(delim, id, 1) - 1) id, delim
    FROM rep2
    WHERE (CHARINDEX(delim, id, 1) > 0)

    UNION ALL

    SELECT RIGHT(id, LEN(id) - CHARINDEX(delim, id, 1)) id, delim
    FROM rep2
    WHERE (CHARINDEX(delim, id, 1) > 0)
)
INSERT #table
(Name
,ID)
SELECT
 r1.name
,r2.id
FROM rep1 r1
CROSS JOIN rep2 r2
LEFT JOIN #table t
ON r2.id=t.id
AND t.name=r1.name
WHERE (CHARINDEX(r1.delim, r1.name, 1) = 0)
AND (CHARINDEX(r2.delim, r2.id, 1) = 0)
AND t.name IS NULL
ORDER BY r1.name
,r2.id
OPTION (MAXRECURSION 0);

Upvotes: 1

mohan111
mohan111

Reputation: 8865

Here we are sepearting Comma Seperated into rows 

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
        DROP TABLE #Temp

    IF OBJECT_ID('tempdb..#NewTemp') IS NOT NULL
        DROP TABLE #NewTemp

    Declare  @Testdata table ( name Varchar(max), Data varchar(max))
    insert @Testdata select 'A', '1,2,3'
    insert @Testdata select 'B', '1,2,3'
    insert @Testdata select 'C', '1,2'
    insert @Testdata select 'A', '1,2,3,4'
    insert @Testdata select 'C', '1,2,3,4,5'

    ;with tmp(name, DataItem, Data) as (
    select name, LEFT(Data, CHARINDEX(',',Data+',')-1),
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
    from @Testdata
    union all
    select name, LEFT(Data, CHARINDEX(',',Data+',')-1),
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
    from tmp
    where Data > ''
    )

Then Inserting into Temp Table 

select DISTINCT name, DataItem INTO #Temp
from    tmp WHERE   EXISTS (Select DISTINCT name,DataItem from tmp)
order by name

Here we are controlling entry of Duplicates we can observe combination won't repeat like (A,1),(B,1)Even though they are multiple 

CREATE TABLE #NewTemp(name Varchar(max), Data varchar(max))
INSERT INTO #NewTemp (name,Data)
Select name,DataItem from #Temp

Select * FROM #NewTemp

Upvotes: 0

Related Questions