6

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?

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
gotqn
  • 42,737
  • 46
  • 157
  • 243

2 Answers2

6

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

Community
  • 1
  • 1
paparazzo
  • 44,497
  • 23
  • 105
  • 176
4

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.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
  • I have only one function. If I use in in this way, will be the result cached, or it will be executed many times for one row only? – gotqn Sep 25 '12 at 12:55
  • If you have only one method to be called to fill up a column, then that function will be called for as many rows as present in the DB. If you want to limit the set of rows for which this should run, you can obviously specify a WHERE clause to qualify those set of rows. – Vikdor Sep 25 '12 at 13:02
  • Yes, I know this. I wanted to execute the function for each row. My point is,that the functions returns 10 columns, not one. – gotqn Sep 25 '12 at 13:12
  • I didn't get that one function call returns all the columns for a given row. Anyway, I see that you got what you wanted from the other answer. – Vikdor Sep 25 '12 at 13:35
  • This is the simplest answer. Updating columns w the output of a function is a very basic, very useful methodology. – greg Aug 31 '16 at 15:31