Tuesday, December 20, 2011

Insert comma-delimited string value to table column in Sql


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
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

No comments :

Post a Comment