INSERT vs. UPDATE

I have the following query:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["chestionar"].ConnectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO Raspunsuri Values(@raspuns,@cnp,@data,'1',@ip,@idsesiune)", con);

cmd.Parameters.AddWithValue("@cnp", Session["sesiune_cnp"]);
cmd.Parameters.AddWithValue("@raspuns", textbox1.Text);
cmd.Parameters.AddWithValue("@data", DateTime.Now.ToLocalTime());
cmd.Parameters.AddWithValue("@ip",ip);
cmd.Parameters.AddWithValue("@idsesiune", id_sesiune);

try
{
    con.Open();
    cmd.ExecuteNonQuery();
    Response.Redirect("User2.aspx");
}
catch (Exception ex)
{
    Console.WriteLine("Error:" + ex);
}
finally
{
    con.Close();
}

What i need is to see if there is any record in the table and if there is than update else insert it.How can I achieve that?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This is probably best done in a Stored Procedure due to the amount of scripting involved (it would be messy inline!).

Pass your parameters to a Stored Procedure and do something like:

IF EXISTS(SELECT cnp FROM Raspunsuri WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="1d7e736d205d7e736d">[email protected]</a>)
BEGIN
    UPDATE ...
    WHERE <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="86e5e8f6bbc6e5e8f6">[email protected]</a>
END
ELSE
BEGIN
    INSERT INTO....
END

Assuming @cnp is your Primary Key

Your SqlCommand would then be changed to:

SqlCommand cmd = new SqlCommand("sp_StoredProcedureName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@cnp", Session["sesiune_cnp"]);
cmd.Parameters.AddWithValue("@raspuns", textbox1.Text);
cmd.Parameters.AddWithValue("@data", DateTime.Now.ToLocalTime());
cmd.Parameters.AddWithValue("@ip",ip);
cmd.Parameters.AddWithValue("@idsesiune", id_sesiune);

Method 2

You can use the Exists function in SQL. For Example

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["chestionar"].ConnectionString);   
    SqlCommand cmd = new SqlCommand("if Exists(Select 1 from Raspunsuri where <your unique criteria>)rn" +
"Update Raspunsuri set <values you want to set> where <your unique criteriarn" +
"elsern" +
"INSERT INTO Raspunsuri Values(@raspuns,@cnp,@data,'1',@ip,@idsesiune)", con);

    cmd.Parameters.AddWithValue("@cnp", Session["sesiune_cnp"]);   
    cmd.Parameters.AddWithValue("@raspuns", textbox1.Text);   
    cmd.Parameters.AddWithValue("@data", DateTime.Now.ToLocalTime());   
    cmd.Parameters.AddWithValue("@ip",ip);   
    cmd.Parameters.AddWithValue("@idsesiune", id_sesiune);

That should do the trick

Method 3

You can use the @@ROWCOUNT feature from SQL Server.

UPDATE Raspunsuri SET (...) WHERE PrimaryKeyColumn='YourValue'
IF @@ROWCOUNT=0
    INSERT INTO Raspunsuri VALUES (...)

Similar question: Insert / Update to Sql

Method 4

What i need is to see if there is any record in the table and if there is than update else insert
it.How can I achieve that?

Write proper SQL?

Basiacll waht you need to forumlate is known as an “Upsert”.

http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm

hasa good explanation.

Method 5

First you check whether the record is present in the table by writing a query as “Select count(*) from tablename where columnvalue=”something”.If count is more than 0 then table has record.So in that case you write an Update statement else write Insert statement. This you can write in your code or by writing a stored procedure.

Method 6

What i need is to see if there is any record in the table and if there
is than update else insert it.How can I achieve that?

I like @Alex’s approach

-- For each row in source
BEGIN TRAN

UPDATE target
SET <target_columns> = <source_values>
WHERE <target_expression>

IF (@@ROWCOUNT = 0)
INSERT target (<target_columns>)
VALUES (<source_values>)

COMMIT

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0