SQL query for insert into with update on duplicate key is error

Question

I got error on the following query

INSERT INTO tableName3 (column1, column2)
    SELECT 
        b.column1,
        SUM (a.column2) AS SumColumn2
    FROM 
        tableName1 AS a
    JOIN 
        tableName2 AS b ON a.column1 = b.column2 
    GROUP BY 
        b.column2
ON DUPLICATE KEY UPDATE tableName3
   SET column2 = SUM (a.column2) AS SumColumn2

The error messages are

Incorrect syntax near the keyword 'ON*'

and

Incorrect syntax near the keyword 'AS'

How to fix it?

Answer 1

First, your select statement is wrong.

SELECT 
    b.column1,
    SUM (a.column2) AS SumColumn2
FROM 
    tableName1 AS a
JOIN 
    tableName2 AS b ON a.column1 = b.column2 
GROUP BY 
    b.column2

this should probably be grouped by b.column1, otherwise you will get an exception since columns in the select clause must appear either in the group by clause or in an aggregating function in sql-server

Second, there is no ON DUPLICATE KEY directive in Sql server. A Quick search found many references for this in mysql, but mysql is not sql-server.

To achieve this kind of behavior in Sql server you should probably use MERGE statement.

Your code should look something like this:

MERGE tableName3  AS target
USING (
    SELECT 
    b.column1,
    SUM (a.column2) AS SumColumn2
    FROM 
    tableName1 AS a
    JOIN 
    tableName2 AS b ON a.column1 = b.column2 
    GROUP BY 
    b.column1
) AS source (column1, SumColumn2)
ON (target.column1= source.column1)
WHEN MATCHED THEN 
    UPDATE SET column2= source.SumColumn2
WHEN NOT MATCHED THEN
    INSERT (column1, column2)
    VALUES (source.column1, source.SumColumn2)