T-SQL ... Notation

Question

When researching ways to optimise bulk updates to SQL Server, I have come across the following query which works correctly but I'm having difficulty understanding how the '...' notation works in the following c# code snippet:

command.CommandText = "CREATE TABLE #TmpTable(...)";
command.ExecuteNonQuery();

command.CommandText = "UPDATE T SET ... FROM " + tableName + " T INNER JOIN #TmpTable Temp ON ...; DROP TABLE #TmpTable;"
command.ExecuteNonQuery();

It means that you have to add some code. For example in your case for the first "..." you have to add something like this --> "CREATE TABLE #TmpTable(ID int not null)". You need to define your fields. — mohabbati, Oct 19 '18 at 10:16
This code was posted in https://stackoverflow.com/questions/20635796/bulk-update-in-c-sharp and I have implemented and tested it in a C# application. My question is why does it work? — nkift, Oct 19 '18 at 10:27
Looking at the code in the question you have linked, the query is executed in a `try...catch` block, and any exceptions raised are silently ignored. If you step through (or add `throw;` to the catch block) I imagine you'll see an exception thrown. — Diado, Oct 19 '18 at 10:38
Thanks for your feedback Diado, however, the values I inserted into #TmpTable are updated correctly in the 'tableName' table and any exceptions would have been logged in my test code. — nkift, Oct 19 '18 at 10:45
I'm inclined to say some other code is updating your table then, as `CREATE TABLE #Test1 (test1 VARCHAR(1)); CREATE TABLE #TmpTable (test2 VARCHAR(1)); UPDATE T SET ... FROM #Test1 T INNER JOIN #TmpTable Temp ON ...;` doesn't parse in SSMS (specifically the `UPDATE` statement throws `Incorrect syntax near the keyword 'FROM'.`). — Diado, Oct 19 '18 at 10:52
Hi Diado, there was a bug in my code and instead of updating the TmpTable, 'tableName' was being directly updated. I incorrectly assumed that the ellipsis was doing some kind of magic. I'm still not sure why a SQL exception was not thrown in this case. It looks like the '...' was used as a placeholder in the referenced example. The correct code should have been command.CommandText = "UPDATE T SET x = Temp.x FROM " + tableName + " T INNER JOIN #TmpTable Temp ON T.Id = Temp.Id; COMMIT; DROP TABLE #TmpTable; COMMIT;"; — nkift, Oct 19 '18 at 11:39
Voting to close, the problem seems to be that the OP literally typed "..." where the example he was copying intended that to mean 'your code here'. — DaveyDaveDave, Oct 19 '18 at 12:59

score 0 · Answer 1 · edited Oct 19 '18 at 13:23

There was a bug in the code so that the destination table was being updated directly instead of writing to the temporary table first. The referenced code was using the ellipsis (...) as a placeholder but for some reason my code failed to throw an exception where it should have done. The corrected snippet should read as follows:

command.CommandText = "CREATE TABLE #TmpTable(Id int, x int)";
command.ExecuteNonQuery();

// Insert updated rows read from 'tableName' into #TmpTable

command.CommandText = "UPDATE T SET T.x = Temp.x FROM " + tableName + " T INNER JOIN #TmpTable Temp ON T.Id = Temp.Id; DROP TABLE #TmpTable;"
command.ExecuteNonQuery();

T-SQL ... Notation

1 Answers1