I want to insert a list of data that separated by comma like ’1234,2345,3456′ into a SQL table. How do I do that? Well, it is not hard if you know the trick. Here are the codes to do it.

DECLARE @pos int, @curruntLocation char(20), @input varchar(2048)
SELECT @pos=0
SELECT @input = ’1234,2345,3456′
SELECT @input = @input + ‘,’

CREATE TABLE #tempTable (temp varchar(100) )

WHILE CHARINDEX(‘,’,@input) > 0
BEGIN
SELECT @pos=CHARINDEX(‘,’,@input)
SELECT @curruntLocation = RTRIM(SUBSTRING(@input,1,@pos-1))
INSERT INTO #tempTable (temp) VALUES (@curruntLocation)
SELECT @input=SUBSTRING(@input,@pos+1,2048)
END

SELECT * FROM #tempTable

DROP TABLE #tempTable

Result – you will get 3 rows (1234,2345,3456) in #tempTable. The @input is your input variable. @curruntLocation is the value that needed to be inserted into SQL table. That’s it. :)

Tags: , , , , , , , , ,

Related Entries