INSERT statement add multiple identical rows

Question

i'm using a webservice to add value inside my database, normaly my query should add only one row but actually it had multiple identical rows.

So here is my 2 tables : Application.sql

Details.sql

My query:

INSERT INTO Details 
(SecurityKey, Name, URL, DateLog, Level, Message, Username, ServerName, Port, ServerAdress, RemoteAdress, Logger, Exception, ApplicationID) 
SELECT  
@SecurityKey, @Name, @URL, @DateLog, @Level, @Message, @Username,@ServerName, @Port, @ServerAdress, @RemoteAdress, @Logger, @Exception, @ApplicationID
FROM Details D
INNER JOIN Application A ON @SecurityKey = A.SecurityKey AND @ApplicationID = A.ID

And my webservice with @ApplicationID=14 and @SecurityKey="shSfnDOIPM":

{
  "DateLog": "27/02/12",
  "Level": "danger",
  "Message": "ytry",
  "Username": "ghth",
  "ServerName": "zrzrzer",
  "Port": "80",
  "ServerAdress": "36zr",
  "RemoteAdress": "12z",
  "Logger": "rg",
  "Exception": "zr",
  "ApplicationID": 14,
  "Name": "zr",
  "SecurityKey": "shSfnDOIPM",
  "URL": "wwww",
}

Normaly with these informations my query should sort only one row but as you can see in the "Details" picture, it create many and many rows...

Thanks for helps

Please stop using comma based Implicit joins and use [Explicit `Join` based syntax](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Madhur Bhaiya, Oct 24 '18 at 12:48

score 1 · Answer 1 · answered Oct 24 '18 at 12:43

1

It looks like you are referencing two tables in your query but you are not joining them together. Possibly getting an cartesian product in the process.

answered Oct 24 '18 at 12:43

Mike

23
4

you're right, how should i remove this cartesian product? – Rquen Oct 24 '18 at 12:47
See SadmirD's answer below. – Mike Oct 24 '18 at 12:56

SadmirD · Answer 2 · 2018-10-24T12:55:28.670

0

You are utilizing INSERT INTO SELECT query, yet you don't issue any JOIN to connect these two tables (either implicit or explicit). When utilizing the select query that affects multiple tables, you need to have N-1 where statements to join them (implicit join), or N-1 of such JOINs, in order to avoid cartesian product. Cartesian product = "multiple inserts" in your case, due to INSERT INTO... SELECT

I guess you need an INNER JOIN and would advise you to avoid implicit joins in general.

An example, editing your query:

INSERT INTO Details 
(SecurityKey, Name, URL, DateLog, Level, Message, Username, ServerName, Port, ServerAdress, RemoteAdress, Logger, Exception, ApplicationID) 
SELECT  
@SecurityKey, @Name, @URL, @DateLog, @Level, @Message, @Username,@ServerName, @Port, @ServerAdress, @RemoteAdress, @Logger, @Exception, @ApplicationID
FROM Details D
INNER JOIN Application A ON D.ApplicationID = A.ID 
WHERE A.SecurityKey = @SecurityKey AND A.ID = @ApplicationID

Notice that I put INNER JOIN and moved your previous "join" into a WHERE statement.

edited Oct 24 '18 at 12:55

answered Oct 24 '18 at 12:50

SadmirD

642
3
8

I took your query but it again created multiples rows :/ – Rquen Oct 24 '18 at 13:03
"Multiple inserts" are also possible again, since you are doing INSERT INTO... SELECT. However, now you shouldn't have "identical entries within the same insert statement. This query will actually copy previous entries from table details that match the where clause and then insert them into same table. – SadmirD Oct 24 '18 at 13:08
But how should I get only one row, without the duplication of identical rows, because the webservice should give me only one return. – Rquen Oct 24 '18 at 13:13
if you receive a DETAIL object that you need to save into your table, then you need to issue INSERT INTO DETAIL VALUES(var1, var2, var3), etc. If you still want to issue insert only for the first value found via INSERT INTO... SELECT, then you need to add LIMIT 1 at the end of your select query – SadmirD Oct 24 '18 at 13:16

INSERT statement add multiple identical rows

2 Answers2