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

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

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
  1. sunlikestar
    March 3, 2010 at 2:58 pm | #1

    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
    
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: