How do I return data from a SQL String_split function into my table

The Split functions can be slow, especially with larger tables.

Consider ParseName(), for example

Declare @String varchar(25) = 'John Smith 999-11-1234'
Select FirstName=PARSENAME(Replace(@String,' ','.'),3)
      ,LastName =PARSENAME(Replace(@String,' ','.'),2)
      ,SSN      =PARSENAME(Replace(@String,' ','.'),1)

--Returns--
FirstName   LastName    SSN
John        Smith       999-11-1234

So, in your case, and assuming your delimiter is a space AND your field looks like "Jame Smith"

Select FirstName=PARSENAME(Replace(YourFieldName,' ','.'),2)
      ,LastName =PARSENAME(Replace(YourFieldName,' ','.'),1)

I think this will work instead:

Create Table #Test (Value Varchar(8000), LeftPart Varchar(8000), RightPart Varchar(8000))
Insert #Test Values ('Joe Cool', '', '')
Insert #Test Values ('John Doe', '', '')


Update #Test Set LeftPart = Substring(Value, 1, Charindex(' ', Value) - 1), RightPart = Substring(Value, Charindex(' ', Value) + 1, 8000) From #Test
Select * From #Test

Also note that unless you plan to drop the full field after the split, this is a good case for computed columns.

If you want to use STRING_SPLIT (the ways with PARSENAME and SUBSTRING are more adequate for this situation) you need to do something like this:

;WITH cte AS (
SELECT  be.*,
        SS.value as FN, 
        ROW_NUMBER() OVER (PARTITION BY be.id ORDER BY (SELECT 1)) as RN
FROM b2bemployee be 
OUTER APPLY STRING_SPLIT(be.firstname, ' ') as SS
WHERE be.id=2319
)

SELECT  c1.*,
        c2.FN
FROM cte c1
LEFT JOIN cte c2
    ON c1.RN+1 = c2.RN
WHERE c1.RN = 1

I would suggest to use LEFT and RIGHT

SELECT  be.*,
        RIGHT(be.firstname,CHARINDEX(' ',be.firstname)) as updatedfirstname ,
        LEFT(be.firstname,LEN(be.firstname) - CHARINDEX(' ',be.firstname)-1) as updatedlastname 
FROM b2bemployee be 
WHERE be.id=2319