Need to insert a row if its not exist and update if exists. I've found this solution for MySQL:
INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE
name="A", age=19
But I can't find similar for MSSQL..
It works fine if the object exists in the database. In case the object does not exist, and you try to drop, you get the following error. To avoid this situation, usually, developers add T-SQL If Exists statement and drop the object if it is already available in the database.
INSERT OR UPDATE table query inserts or updates rows of data the come from the result set of a SELECT query. The columns in the result set must match the columns in the table. You can use INSERT OR UPDATE with a SELECT to populate a table with existing data extracted from other tables.
You can use 2 statements (INSERT
+ UPDATE
) in the following order. The update won't update anything if it doesn't exist, the insert won't insert if it exist:
UPDATE T SET
name = 'A',
age = 19
FROM
[table] AS T
WHERE
T.id = 1
INSERT INTO [table] (
id,
name,
age)
SELECT
id = 1,
name = 'A',
age = 19
WHERE
NOT EXISTS (SELECT 'not yet loaded' FROM [table] AS T WHERE T.id = 1)
Or a MERGE
:
;WITH ValuesToMerge AS
(
SELECT
id = 1,
name = 'A',
age = 19
)
MERGE INTO
[table] AS T
USING
ValuesToMerge AS V ON (T.id = V.id)
WHEN NOT MATCHED BY TARGET THEN
INSERT (
id,
name,
age)
VALUES (
V.id,
V.name,
V.age)
WHEN MATCHED THEN
UPDATE SET
name = V.name,
age = V.name;
I prefer checking the @@ROWCOUNT
. It's a much more compact solution.
UPDATE table set name = 'A', age = 19 WHERE id = 1;
IF @@ROWCOUNT = 0
INSERT INTO table (id, name, age) VALUES(1, "A", 19);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With