Insert or update from a table

Question

Let me start by saying that I am very new when it comes to SQL, but I picked it up pretty quickly. I am using SQL Server.

What I need to do is insert several records from another table and, where there is a duplicate, update the current record from the same table instead.

Naked with me:

I found some useful answers on this site, but no one works for me. Everyone returns a syntax error, and I'm not sure if this is just the interface that I use that does not support these commands or what. Inserting to where there is no duplicate key, this request works fine. If anyone could help me, what syntax could be used to correctly execute these commands with the given, I would be incredibly grateful!

Here are the ones that seem to be doing exactly what I need, but didn't work.

REPLACE INTO
INSERT ... ON DUPLICATE KEY UPDATE
INSERT OR REPLACE INTO

Here's an example of what my INSERT query looks like:

---

USE database
GO
INSERT INTO products
(upc, name, price)
    select upc = TempTables.dbo.new_items.upc,
    name = TempTables.dbo.new_items.name,
    price = TempTables.dbo.new_items.price
FROM TempTables.dbo.new_items

Answer 1

I personally like the operator MERGE

create table ##new_items (upc int, name int, price int)

create table ##products
(upc int, name int, price int)

merge into ##products [tgt]
using ##new_items [src]
on [tgt].upc = [src].upc and [tgt].name = [src].name 
when matched then update set price = [src].price 
when not matched then insert (upc, name, price) 
    values ([src].upc, [src].name, [src].price)
;

To view the items you are working with, you can request these

-- the items in new_items that aren't in products
select n.* from new_items n left outer join products p 
on n.upc = p.upc where p.upc is null

-- the items in new_items that are in products
select n.* from new_items n left outer join products p 
on n.upc = p.upc where p.upc is not null

Of course, after starting the update / insert / merge, you will not know which elements were already there and which were inserted. If you want to know that you need to add the “updated date” column to the table and mark it at that time.

Answer 2

What I need to do is insert several records from another table and, where there is a duplicate, update the current record from the same table instead.

( SQL- , , - )

(assuming the key is upc):

UPDATE products
SET name = new_items.name,
    price = new_items.price
FROM products
INNER JOIN new_items
   ON products.upc = new_items.upv 

INSERT INTO products
(upc, name, price)
SELECT 
    upc,
    name,
    price
FROM TempTables.dbo.new_items
WHERE upc NOT IN 
    (SELECT upc FROM products)

Answer 3

UPDATE products  
SET name = new_items.name, price = new_items.price
FROM products JOIN new_items USING(upc);

INSERT INTO products
(upc, name, price)
SELECT upc, name, price
FROM TempTables.dbo.new_items
WHERE NOT EXISTS (SELECT 1 FROM products WHERE upc = TempTables.dbo.new_items.upc);

Given that upc is yours Primary key,

Here the request UPDATEwill succeed if there is already a line withupc in new_items

INSERTthe request will succeed if line c upc in new_itemsdoes not exist.