我有一个表结构为,
Table 3
Fruit ID - Foreign Key (Primary Key of Table 1)
Crate ID - Foreign Key (Primary Key of Table 2)
现在我需要执行一个查询,
Update Crate ID
of Fruit ID
if Fruit ID
已经在表中,如果没有则将记录插入表3中作为新记录。
这就是我现在在代码中得到的,
private void RelateFuirtWithCrates(List<string> selectedFruitIDs, int selectedCrateID)
{
string insertStatement = "INSERT INTO Fruit_Crate(FruitID, CrateID) Values " +
"(@FruitID, @CrateID);"; ?? I don't think if it's right query
using (SqlConnection connection = new SqlConnection(ConnectionString()))
using (SqlCommand cmd = new SqlCommand(insertStatement, connection))
{
connection.Open();
cmd.Parameters.Add(new SqlParameter("@FruitID", ????? Not sure what goes in here));
cmd.Parameters.Add(new SqlParameter("@CrateID",selectedCrateID));
}
您可以使用以下命令进行“upsert”MERGE
SQL Server 中的语法:
MERGE [SomeTable] AS target
USING (SELECT @FruitID, @CrateID) AS source (FruitID, CrateID)
ON (target.FruitID = source.FruitID)
WHEN MATCHED THEN
UPDATE SET CrateID = source.CrateID
WHEN NOT MATCHED THEN
INSERT (FruitID, CrateID)
VALUES (source.FruitID, source.CrateID);
否则,您可以使用类似以下内容:
update [SomeTable] set CrateID = @CrateID where FruitID = @FruitID
if @@rowcount = 0
insert [SomeTable] (FruitID, CrateID) values (@FruitID, @CrateID)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)