How has SQL Server not gone the way of the dinosaur?

[SlurrerOfSpeech]

I use SQL Server at my job and for my personal projects (only because I'm familiar with it). Nevertheless I am always amazed at how the language doesn't provide any simple ways of solving common problems.

For example, let's say I have some data which represents rows I want to insert or update in another table. I first have to create a temp table with the same column definition as the target table. This is a lot of busy-work when the target table has dozens of columns. Then I have to insert all this data into the temp table. Then I have to use a MERGE statement which involves a lot of syntax and, by the way, can't UPDATE more than 1 column! You are out of luck if you want to update both "EmailAddress" and "FirstName" for any MATCHED "UserId". I've seen users on MSDN and TechNet as far back as 2007 request to Microsoft that they change this. It remains the same.

Another example. I want to iterate through a list of values in a column, basically the equivalent of

for(int id in MyTable.Select(t => t.Id))
{
    // ... 
}

Here's how you to that in T-SQL:

    CREATE TABLE #ids (rn INT, id INT)
    INSERT INTO #ids (rn, id)
    SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY Id), Id
    FROM MyTable
    DECLARE @i INT = 0
    DECLARE @n INT = (SELECT COUNT(*) FROM #ids)
    WHILE(@i < @n)
    BEGIN
         DECLARE @id INT = (SELECT TOP 1 id FROM #ids WHERE rn = @k)
         // ... 
         @i = @i + 1
    END

and that's probably wrong and there's no way to step-through debug it.

 

[jedishrfu]

The power of sql is not in doing row by row stuff but in manipulating whole tables and views to get what you want. It's akin to manipulating sets of data collecting, transforming , grouping and/or sorting it to create a report or input to another program.

Each SQL statement is a program unto itself especially the select statement.

 

[jack action]

You have to remember that SQL is a specialized program that is optimized to manipulate tables of data. So if your data is not already in a table, chances are that creating one will be required to optimize your job.

by the way, can't UPDATE more than 1 column! You are out of luck if you want to update both "EmailAddress" and "FirstName" for any MATCHED "UserId".

I'm more familiar with MySQL, but it seems that T-SQL also allow updating a column list, such as this example:

MERGE Production.ProductInventory AS target 
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod 
   JOIN Sales.SalesOrderHeader AS soh 
   ON sod.SalesOrderID = soh.SalesOrderID 
   AND soh.OrderDate = @OrderDate 
   GROUP BY ProductID) AS source (ProductID, OrderQty) 
ON (target.ProductID = source.ProductID) 
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0 
   THEN DELETE 
WHEN MATCHED   
   THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty,   
                   target.ModifiedDate = GETDATE() 
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID, 
   Deleted.Quantity, Deleted.ModifiedDate;

For the 2nd example, you are not really fair in your comparison. First you are missing some declarations in your FOR...LOOP, like MyTable. You are also ordering your table in the T-SQL, but not in your FOR...LOOP. Can't you store your SELECT into a variable like @mytable that would act as your MyTable variable, for example? Then you would be able to access your data. Here is a good example, I think:

DECLARE @CursorVar CURSOR; 

SET @CursorVar = CURSOR SCROLL DYNAMIC 
FOR 
SELECT LastName, FirstName 
FROM AdventureWorks2012.HumanResources.vEmployee 
WHERE LastName like 'B%'; 

OPEN @CursorVar; 

FETCH NEXT FROM @CursorVar; 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    FETCH NEXT FROM @CursorVar 
END; 

CLOSE @CursorVar; 
DEALLOCATE @CursorVar;

Look into CURSOR as well for more info.

 

[newjerseyrunner]

I'm confused about what you are trying to do. Can you give an example of the before and after tables? I don't understand what you mean that you can't update multiple columns at once.

UPDATE 
    Table_A 
set 
    EmailAddress=test@test.com, 
    FirstName = Table_B.FirstName
FROM
   Some_Table AS Table_A
   INNER JOIN Other_Table AS Table_B
       ON Table_A.UserId = Table_B.UserId

Also, a lot of people are getting away from procedural SQL and using DOAs like Doctrine.