I think you're making this more complicated than it needs to be! You can construct the WKT representation of a point by concatenating together the string values of each coordinate, and then passing that to the STPointFromText() method, as follows:
INSERT INTO NewTable (point)
SELECT
geometry::STPointFromText('POINT('+ CAST(X AS varchar(255)) + ' ' + CAST(Y AS varchar(255)) + ')',0)
FROM
OldTable
Alternatively, if it's only points that you're creating, you'll probably find it easier to use the Point() method instead. Point() accepts two floating point coordinate values directly (X/Y, or Lat/Lng), so you don't have to bother with the CASTing:
INSERT INTO NewTable (point)
SELECT
geometry:: Point(X,Y,0)
FROM
OldTable
Note: You should not include a space between the double colons of geometry:: and the word Point in the above example - it's just that I needed to insert the space to stop the emoticon 😛 appearing!
------
Alastair Aitchison
Beginning Spatial with SQL Server 2008