如何在SQL表中同时更新现有记录并插入新记录？ SQL [英] How to update existing record and insert new record at same time in SQL table ? SQL

问题描述

我有一个表。我想更新表。实际上我有一个gridview从SQL表中检索值。当页面加载然后gridview加载值。我希望当我在gridview中插入新值然后在SQL表中使用SINGLE查询更新现有值并使用新值插入同一个表。我可以这样做吗？告诉我在C＃，ASP.NET中使用的SQL查询。我要求UPDATE现有记录并在其中插入新记录同一个表使用一个查询同时。谢谢



我尝试过：

I have a table.I want to update the table.Actually I have a gridview which retrieve values from SQL table.When page load then gridview load the values .I want that when i insert new values in gridview then in SQL Table existing values update and also new values insert in the same table using SINGLE query.How can i do this?Just tell me SQL query which works in C#,ASP.NET.I'm asking for UPDATE existing record and insert new record in the same table at same time using one query. Thanks

What I have tried:

public void insert(object sender, EventArgs e)
{
 string user = Session["name"].ToString();
 SqlConnection cnn = new SqlConnection("Data Source=HAMEED_KHAN\\SQLEXPRESS;Initial Catalog=db_compiler;Integrated Security=True");
SqlCommand cmd3 = new SqlCommand("SELECT User_ID from tbl_user WHERE User_Name='" + user + "'", cnn);
cnn.Open();
string id = cmd3.ExecuteScalar().ToString();
int ID = Int32.Parse(id);
Session["ID"] = ID;
string d = Session["value"].ToString();
SqlCommand cmd2 = new SqlCommand("SELECT Database_id FROM Create_db WHERE Database_Name='" + d + "'", cnn);
Response.Write("<script>Var Z=Prompt('Enter Table Name');</script>");
string dbid = cmd2.ExecuteScalar().ToString();
cnn.Close();
int D_ID = Int32.Parse(dbid);
string str = "";
string type = "";
for (int i = 0; i < GridView2.Rows.Count; i++)
{
 str = GridView2.Rows[i].Cells[1].Text.ToString();
 type = GridView2.Rows[i].Cells[2].Text.ToString();
 string Name = GridView2.Rows[i].Cells[1].Text.ToString();
 string Type = GridView2.Rows[i].Cells[2].Text.ToString();
 string size = GridView2.Rows[i].Cells[3].Text.ToString();
 CheckBox allow = GridView2.Rows[i].Cells[4].Controls[0] as CheckBox;
 CheckBox primary = GridView2.Rows[i].Cells[5].Controls[0] as CheckBox;
 string UserID = Session["ID"].ToString();
 int UID = Int32.Parse(UserID);
 string date = DateTime.Now.ToString();
 string A = (allow.Checked == true ? "NULL" : "NOT NULL");
 string P = (primary.Checked == true ? "PRIMARY KEY" : "");
 string Table = Session["TBL_NAME"].ToString();
 string queryy ="USE db_compiler UPDATE tbl_field SET Column_Name='" + Name + "', Data_Type='" + Type + "',Size='" + size + "',Database_id='" + D_ID + "',Allow_Null_='" + (allow.Checked == true ? "true" : "false") + "',Primary_Key_='" + (primary.Checked == true ? "true" : "false") + "',User_id='" + UID + "',Date='" + date + "' WHERE Table_Name='" + Table + "' IF @@ROWCOUNT=0 insert into tbl_field (Table_Name,Column_Name,Data_Type,Size,Database_id,Allow_Null_,Primary_Key_,User_id,Date) VALUES('" + Table + "','" + Name + "','" + Type + "','" + size + "','" + D_ID + "','" + (allow.Checked == true ? "true" : "false") + "','" + (primary.Checked == true ? "true" : "false") + "','" + UID + "','" + date + "')";
  SqlCommand cmd = new SqlCommand(queryy, cnn);
  SqlDataAdapter ad = new SqlDataAdapter(cmd);
 cnn.Open();
  cmd.ExecuteNonQuery();
  cnn.Close();
}
}
gridview-image Table name is 'e

gridview_image 表名是'员工'首先我在gridview'名称'，'id'，'地址'中有3行，当我插入新行'ph'并点击'update tabe'然后我更新所有行使用'ph' db_image

gridview_imageTable name is 'employee' first i have 3 rows in gridview 'Name','id','address' when i insert new row 'ph' and click on 'update tabe' then i update all rows with 'ph'db_image

推荐答案

也许是可以用作UPSERT命令的MERGE命令是你需要的。

参见这篇文章：使用MERGE语句执行UPSERT& mdash; DatabaseJournal.com [ ^ ]

Maybe the MERGE command which can be used as "UPSERT" command is what you need.
See this article: Using the MERGE Statement to Perform an UPSERT — DatabaseJournal.com[^]

这篇关于如何在SQL表中同时更新现有记录并插入新记录？ SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！