Insert comma-delimited string value to table column in Sql

Home > Sql Server > Insert comma-delimited string value to table column in Sql

Insert comma-delimited string value to table column in Sql

March 3, 2010 pankajlalwani Leave a comment Go to comments

I have string like ’1,22,3,49,6,7,8′

I want to insert each comma separated value in a table column…my output look like this:

COLUMN
1
22
3
49
6
7
8

Here is the solution:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: <Author,,Name>
– Create date: <Create Date,,>
– Description: <Description,,>

– =============================================
create PROCEDURE [dbo].[sp_InsertCommaSeparatedValues]

AS
BEGIN

DECLARE @FormId VARCHAR(5000)
DECLARE @FormRead varchar(5000)
DECLARE @FormWrite varchar(5000)

SET @FormId = ’1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,’
SET @FormRead =’1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1,’
SET @FormWrite = ’1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1,’

WHILE (CHARINDEX(‘,’, @FormId) > 0)
BEGIN

DECLARE @Form VARCHAR(30)
SET @Form = SUBSTRING(@FormId,0, CHARINDEX(‘,’, @FormId))
PRINT @Form

DECLARE @FRead VARCHAR(30)
SET @FRead = SUBSTRING(@FormRead,0, CHARINDEX(‘,’, @FormRead))
PRINT @FRead

DECLARE @FWrite VARCHAR(30)
SET @FWrite = SUBSTRING(@FormWrite,0, CHARINDEX(‘,’, @FormWrite))
PRINT @FWrite

INSERT INTO tbl_UserFormRights(Fk_UserId,Fk_FormId,[Read],Write)
VALUES(1,@Form,@FRead,@FWrite)

SET @FormId = SUBSTRING(@FormId, CHARINDEX(‘,’, @FormId) + 1, LEN(@FormId))
SET @FormRead = SUBSTRING(@FormRead, CHARINDEX(‘,’, @FormRead) + 1, LEN(@FormRead))
SET @FormWrite = SUBSTRING(@FormWrite, CHARINDEX(‘,’, @FormWrite) + 1, LEN(@FormWrite))

END

END

About these ads

Comments (1) Trackbacks (0) Leave a comment Trackback

sunlikestar

March 3, 2010 at 2:58 pm | #1

Reply | Quote

Why insert one row at a time when you can bulk insert. You can use this function to split your string.

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

	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

Usage with your input:

SELECT * FROM dbo.Split(‘1,22,3,49,6,7,8′, ‘,’)

Returns:
Id Data
1 1
2 22
3 3
4 49
6 6
7 7
8 8

For your insert (I may be misunderstanding what you are trying to do here):

INSERT INTO tbl_UserFormRights(Fk_UserId,Fk_FormId,[Read],Write)
SELECT 1, S1.ID, S1.Data, S2.Data
FROM dbo.Split(’1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1’, ‘,’) S1
INNER JOIN dbo.Split(‘1,0,1,0,1,0,1,0,0,0,1,1,1,1,1,1,1,1,1’, ‘,’) S2 
ON S1.ID = S2.ID

No trackbacks yet.

	ciburuan on Age Calculation from Date of B…
	ahsan on Reset/Clear all fields –…
	rajani on Solving Calendar Extender Prob…
	Tanuj Kumar on Textbox Watermark using Ajaxto…
	sanad on Limiting the number of records…

Pankaj Lalwani’s Blog