T-SQL table columns using function

Question

I have the following table:

RecordID Name Col1 Col2 .... ColN 

RecordID BIGINT PRIMARY KEY CLUSTERED IDENTITY(1,1) and RecordID and Name initialized. The remaining columns are NULL.

I have a function that returns information about other columns on Name .

To initialize my table, I use the following algorithm:

• Create a LOOP
• Get a string, select its value. Name
• Execute the function using the selected name and save its result in temp variables
• Insert temporary variables into the table
• Go to the next entry

Is there a way to do this without a loop?

Answer 1

Basically, a cross application was used 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 update statement

Answer 2

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

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

When inserting new records, assuming RecordID is automatically 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.