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
Hi vivienne! Is this question still open? Do you need further help? Please be aware of the fact, that most professionals giving answers on SO are hungry for reputation points. [Please read this: someone-answers](http://stackoverflow.com/help/someone-answers). Thx! — Shnugo, Apr 20 '16 at 11:44

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

704,970
168
1,303
1,425

answered Apr 18 '16 at 08:07

Phoenician

192
1
2
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