You only have one set of data, your problem is that the select you use to get the data is in the loop, execute this:
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500)
DECLARE @StartingPos INT,@RecPos INT
DECLARE @temptable TABLE(COMPLEXIONID INT)
SET @StartingPos= 1
SET @TEST='1,3,4,'
WHILE
@StartingPos<=LEN(@Test)
BEGIN
SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)
SET @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
PRINT @RecSet
BEGIN
INSERT INTO @temptable VALUES(@RecSet)
SELECT @StartingPos=@RecPos+1
END
END
SELECT * FROM @temptable
I've moved the select to the last line.