You'd probably have to do something like this (C developers know and hate this ):

CREATE TABLE #destination_table(
number INT
)
DECLARE @position INT
DECLARE @source_string VARCHAR( 1000 )
SET @source_string = "1,2,3,4,5,6"
SET @position = CHARINDEX( ",", @source_string )
WHILE @position <> 0
BEGIN
-- You'll probably have to CAST the LEFT since number is an INT,
-- or you can change number in #destination_table to be a varchar
INSERT INTO #destination_table VALUES( LEFT( @source_string, @position ) )
-- I can't recall if STUFF actually modifies @source_string,
-- so the assignment may be unnecessary
SET @source_string = STUFF( @source_string, 0, @position, NULL )
END