I have the following input.

INPUT:

TableA
ID       Sentences
---     ----------

1            I am a student
2            Have a nice time guys!

What I need to do is to extract the words from the sentence(s)

and insert each individual word in another table

OUTPUT:

SentenceID  WordOccurance   Word
----------  ------------    -----
1                1             I
1                2             am
1                3             a
1                4            student
2                1            Have
2                2            a
2                3            nice
2                4            time
2                5            guys!

I was able to get the answer by using the below query

;With numCTE As
    ( 
    Select rn = 1 
    Union all
    Select rn+1 from numCTE where rn<1000)
select 
  SentenceID=id, 
  WordOccurance=row_number()over(partition by TableA.ID order by rn), 
  Word = substring(' '+sentences+' ', rn+1, charindex(' ',' '+sentences+' ', rn+1)-rn-1) 
from 
  TableA 
join numCTE on rn <= len(' '+sentences+' ') 
where    
  substring(' '+sentences+' ', rn,1) = ' ' 
order by  
  id, rn 

How can I improve this query of mine.?

Basically I am looking for a better solution than the one presented

Thanks