更新记录(如果存在的话)如果没有插入 [英] update record if it exists & insert if it does not

问题描述

如果记录存在，我必须使用SQL SERVER 2000并形成查询以使用新数据更新记录，否则将其插入表中.

I have to use a SQL SERVER 2000 & form a query to update the record with new data if record exists, else insert it into the table.
Can it be done without using stored procedure.

推荐答案

saloni15写道:

saloni15 wrote:

form如果记录存在，则用新数据更新记录的查询，否则将其插入表中.不用存储过程就可以做到..

form a query to update the record with new data if record exists else insert it into table. can it be done without using stored procedure..

这完全取决于您为记录定义的唯一性.

如果它是简单的唯一性(仅一个字段左右)，那么唯一性约束可能对您有用....
但是
如果它具有复杂的唯一性(或需要对整个行数据进行匹配)，则最好在Businesslogic(如果在代码中)或SQL函数(如果在DB中)中进行验证...

因此，无需使用存储的proc，就可以完成，但是要取决于您的要求！

It totally depends on the uniqueness you define for the records.

If its a simple uniqueness(just a field or so...) then a unique constraint might do for you....
but
If its a complex uniqueness(or an entire row data needs to matchup), it might be good to verify that in Businesslogic(if in code) or a SQL function (if in DB)...

So, without using stored proc, it can be done but would depend on your requirement!

在没有多个查询的情况下，您无法在SQL Server 2000中执行合并.如果您可以处理多个查询，则可以采用以下两种方法.

• 方法1.
  

You can''t perform a merge in SQL Server 2000 without multiple queries. If you are fine with multiple queries, then here are two approaches you could take.

• Approach #1.
  

-- First query.
INSERT INTO SomeTable (...) VALUES (...)
WHERE NOT EXISTS (SELECT 0 FROM SomeTable WHERE ...)
-- Second query.
UPDATE SomeTable SET ...

方法2.

Approach #2.

-- First query.
SELECT
    CASE
        WHEN EXISTS (SELECT 0 FROM SomeTable WHERE ...) THEN 'update'
        ELSE 'insert'
    END
-- Second query (if first returned "insert").
INSERT INTO SomeTable (...) VALUES (...)
-- Second query (if first returned "update").
UPDATE SomeTable SET ...

SqlConnection conn = new SqlConnection("...");
SqlCommand cmd = new SqlCommand(@"IF EXISTS(SELECT 0 FROM SomeTable
WHERE ID = 0) BEGIN UPDATE SomeTable SET ID = ID + 1 END
ELSE BEGIN INSERT INTO SomeTable(ID) VALUES(0) END", conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

这篇关于更新记录(如果存在的话)如果没有插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！