Insert into localTable (columns) Select col1, col2, col3, CASE WHEN subquery1result > subquery2result THEN subquery1result ELSE subquery2result
END As col4 From server.schema.dbo.table1 Where [stuff]
To flesh that out a little more, if I filled in those subqueries in-line:
Insert into localTable (columns)Select col1, col2, col3, CASE WHEN (select MAX(T2.datecol) from server.schema.dbo.table2 T2 where T2.id = T1.id) > (select MAX(T3.datecol) from server.schema.dbo.table3 T3 where T3.id = T1.id) THEN (select MAX(datecol) from server.schema.dbo.table2 T2 where T2.id = T1.id) ELSE (select MAX(T3.datecol) from server.schema.dbo.table3 T3 where T3.id = T1.id) END As col4 From server.schema.dbo.table1 T1 Where [stuff]
I would normally do these subqueries as user-defined scalar functions, but I can't seem to make one that queries tables in a linked server. And I'm not allowed to modify the schema of the database on the linked server (vendor system).
Any ideas on another way of doing this? I suppose I should copy all relevant values into a holding table (on the local server) & then run the import from there instead of importing directly from the linked server. I was just hoping I could keep it as a straight 'Insert Into... Select From' query in a single step.
I think I stumbled upon a "good enough" solution (which suitably horrify a proper SQL-Ninja):
Insert into localTable (columns) Select col1, col2, col3, (Select MAX(DC) From ( select MAX(T2.datecol) as DC from server.schema.dbo.table2 T2 where T2.id = T1.id UNION select MAX(T3.datecol) as DC from server.schema.dbo.table3 T3 where T3.id = T1.id ) as U ) As col4 From server.schema.dbo.table1 T1 Where [stuff]