SQL query insert and update on duplicate key

Question

I have aTable. aTable has the following records:

    +----+------+------------------+--------+
    | No | Name | Date(mm/dd/yyyy) | Salary | 
    +----+------+------------------+--------+
    | 1  | Ed   |    04/01/2016    | 1000   |
    | 2  | Tom  |    04/02/2016    | 1500   |
    +----+------+------------------+--------+

How about the SQL Server query to produce these results to other table:

+----+------+------------------+--------+---+
| No | Name | Date(yyyy/mm/dd) | Salary | k |
+----+------+------------------+--------+---+
| 1  | Ed   |    04/01/2016    | 1000   | 0 |
| 2  | Tom  |    04/02/2016    | 1500   | 0 |
+----+------+------------------+--------+---+

and update when duplicate key. The primary key is No and Name

why not use the merge statement as I've shown you in a [previous question?](http://stackoverflow.com/a/36678294/3094533) — Zohar Peled, Apr 18 '16 at 08:19

score 1 · Answer 1 · answered Apr 18 '16 at 08:06

You want to produce exactly the same data as your table in a new table only with a new column k which is "0" in any case?

SELECT *,0 AS k
INTO TheNewTable
FROM YourTable;

Then try it out with

SELECT * FROM TheNewTable;

But - to be honest - this seems quite strange...

score 0 · Answer 2 · edited Apr 18 '16 at 11:08

0

Try this :

Insert into second_table_name(No, Name, Date, Salary, k) 
    select 
        No, Name, Date, Salary, 0 
    from aTable

edited Apr 18 '16 at 11:08

marc_s

732,580
175
1,330
1,459

answered Apr 18 '16 at 08:07

Elie M

263
1
3
15

score 0 · Answer 3 · answered Apr 18 '16 at 09:08

The primary key is UNIQUE so you can't duplicate it. Or maybe your logical key is other combination for example Name, Date, Salary then example query could be like this:

MERGE aNewTable as Target
USING 
(
    SELECT Name, Date, Salary, CASE WHEN Count(*) > 1 THEN 1 ELSE 0 END as K
    FROM aTable
    GROUP BY Name, Date, Salary
) as Source ON Source.Name=Target.Name AND Source.Date=Target.Date AND Source.Salary=Target.Salary
WHEN NOT MATCHED THEN
    INSERT (Name, Date, Salary, K)
    VALUES (Source.Name, Source.Date, Source.Salary, Source.K)
WHEN MATCHED THEN
    UPDATE 
    SET K = Source.K
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

or simple to view:

SELECT Name, Date, Salary, CASE WHEN Count(*) > 1 THEN 1 ELSE 0 END as K
FROM aTable
GROUP BY Name, Date, Salary

SQL query insert and update on duplicate key

3 Answers3