T-SQL Update table columns using function

Question

I have the following table:

RecordID 
Name
Col1
Col2
....
ColN

The RecordID is BIGINT PRIMARY KEY CLUSTERED IDENTITY(1,1) and RecordID and Name are initialized. The other columns are NULLs.

I have a function which returns information about the other columns by Name.

To initialized my table I use the following algorithm:

1. Create a LOOP
2. Get a row, select its Name value
3. Execute the function using the selected name, and store its result in temp variables
4. Insert the temp variables in the table
5. Move to the next record

Is there a way to do this without looping?

Answer 1

You can simply say the following if you already have the records in the table.

UPDATE MyTable
SET 
    col1 = dbo.col1Method(Name),
    col2 = dbo.col2Method(Name),
    ...

While inserting new records, assuming RecordID is auto-generated, you can say

INSERT INTO MyTable(Name, Col1, Col2, ...)
VALUES(@Name, dbo.col1Method(@Name), dbo.col2Method(@name), ...)

where @Name contains the value for the Name column.

Answer 2

Cross apply was basically built for this

SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

Using APPLY

UPDATE some_table
SET some_row = another_row,
    some_row2 = another_row/2
FROM some_table st
  CROSS APPLY
    (SELECT TOP 1 another_row FROM another_table at WHERE at.shared_id=st.shared_id)
WHERE ...

using cross apply in an update statement