The Dummy ‘DUMMY’ Table
A problem I have encountered today seems to be a kind of “magic”: A Single INSERT statement that was checked for uniqueness was inserting duplicate rows into the table for each insert. All other parties in the system (Client, Server, etc.) sent the data only once and could not be the problem.
Investigating the SQL Statement behind this insert shows this query (data and structure were changed):
INSERT INTO TableX (A, B, C) SELECT ‘X’ AS A, 2 AS B,’2008-11-19 ‘AS C FROM DUMMY WHERE NOT EXISTS (SELECT * FROM TableX WHERE ID=@ID)
Checking the data on TableX and the data sent, did not revealed the problem.
But, it seems that we had a table called “Dummy” for this query to work… investigating that table revealed the problem:
The ‘dummy’ table contains 1 nvarchar column for trash data. This trash data was there to hold the table with 1 row (so selecting will retrieve data). I have found that this table contained 2 rows (instead of 1 assumed) and it doubled the results of the SELECT ‘X’ AS A… query, so rows was duplicated.
Why?
Traditionally there were several DBMSs that could not understand a query like:
SELECT ‘John Smith’ AS Name, 45 AS Age WHERE 1=1
* Of-course the where clause always evaluates to true, but it is for example purpose.
The reason for this is that this query does not contain the “FROM” clause to specify the table to take the data from. Fortunately, in SQL Server (7+) it is working well and we don’t need to add the FROM clause as the SELECT only specify constants (it will work also for setting parameters/variables values).
Trying to solve this, a developer created a table called “Dummy” so there is always valid “FROM DUMMY” clause.
Solution
The solution, in SQL Server, is to just omit the Dummy table, you don’t need to specify source table if you didn’t specify columns. The new query should be re-written like:INSERT INTO TableX (A, B, C) SELECT ‘X’ AS A, 2 AS B,’2008-11-19 ‘AS CWHERE NOT EXISTS (SELECT * FROM TableX WHERE ID=@ID)
(And of course, the ‘DUMMY’ table should be gone…)