CODEWITHSUNDEEP
c#asp.netsqlsql-server
Master Yoda
Master Yoda

Reputation: 4422

SQL INSERT INTO statement with WHERE Statement

Can you use a WHERE statement within an INSERT INTO statement in SQL?

here is what i am currently trying to do.

INSERT INTO AssetComponents(ComponentID, ComponentDescription)
VALUES (@ComponentType, @CompDescr)
WHERE (AssetTagNumber = @TagNo)

But the compiler is having an issue with the WHERE statement.

thanks

***UPDATE****

This is the full code that i am using so far with amendments

   protected void AddBut_Click(object sender, EventArgs e)
    {
        //still passing the Asset tag number forward here
        var ID = Request.QueryString["Id"];

        string sql = "";

        using (SqlConnection con = new SqlConnection("Data Source: *******************)
        {

            sql = "IF (AssetTagNumber = @TagNo) " +
                   "BEGIN " +
                   "INSERT INTO AssetComponents(ComponentID, ComponentDescription) " + 
                   "VALUES (@ComponentType, @CompDescr) " +
                   "END ";

            using (SqlCommand cmd = new SqlCommand(sql, con))
            {

              //  try
               // {
                    cmd.Parameters.AddWithValue("@TagNo", ID);
                    cmd.Parameters.AddWithValue("@ComponentType", TypeDDL.Text.Trim());
                    cmd.Parameters.AddWithValue("@CompDescr", DescrTB.Text.Trim());

                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    Response.Redirect("ComponentDetails.aspx");
             //   }
            //    catch (SqlException ex) { MessageBox.Show(" "); }
            //    catch (Exception ex) { MessageBox.Show(" "); }
            }
        }
    }

Im sorry i was not clear enough first time around.

What i want to do is insert a new record with a clause that says if this record has an existing PK then use this key to insert another entry for that record

Apologies once again

Upvotes: 0

Views: 4423

Answers (6)

Karan Gandhi
Karan Gandhi

Reputation: 1494

if EXISTS (select * from AssetComponents where AssetTagNumber = @TagNo)

Begin

INSERT INTO AssetComponents(ComponentID, ComponentDescription)
(@ComponentType, @CompDescr)

End

Upvotes: 1

cmenke
cmenke

Reputation: 164

Consider INSERT SELECT:

INSERT INTO AssetComponents(ComponentID, ComponentDescription)
SELECT [fill out here] AS ComponentID,
       [fill out here] AS ComponentDescription
FROM somesource
WHERE [condition]

This is a specialty of MS SQL Server so will not work in other databases. It sort of requires that your data are already in another table or other source that you can query.

Upvotes: 0

NeverHopeless
NeverHopeless

Reputation: 11233

WHERE clause is something that helps to filter record, so it preferably uses with either SELECT or UPDATE. For INSERT we normally use IF NOT EXISTS clause.

See Examples:

  1. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/724ab6f3-413f-4c59-9b68-776f3ecfa899/insert-if-not-exists-into

  2. http://msdn.microsoft.com/en-us/library/ms174335.aspx

Also, after looking at documentation, we can see that INSERT statement has NO support for WHERE clause.

If records already exists you can perform eith UPDATE or DELETE with INSERT operations.

You can try like:

IF NOT EXISTS (SELECT * FROM AssetComponents WHERE (AssetTagNumber = @TagNo))
 INSERT INTO AssetComponents(ComponentID, ComponentDescription) VALUES (@ComponentType, @CompDescr)
ELSE
 --UPDATE fields

Upvotes: 0

Ubaid Ashraf
Ubaid Ashraf

Reputation: 885

Use this: 

UPDATE AssetComponents     
Set ComponentID=@ComponentType, ComponentDescription=@CompDesc    
Where AssetTagNumber = @TagNo

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

You can not "conditionally insert" like that. The WHERE clause is only available for SELECT, UPDATE or DELETE.

To check whether you need to INSERT a new record, you need to use IF, as in:

IF NOT EXISTS (SELECT ...)
    INSERT INTO ...

Upvotes: 2

cycaHuH
cycaHuH

Reputation: 3470

Why don't you just use IF-clause?

IF (AssetTagNumber = @TagNo)
BEGIN
    INSERT INTO AssetComponents(ComponentID, ComponentDescription)
    VALUES (@ComponentType, @CompDescr)
END

For statements with WHERE script should look similar to:

INSERT INTO AssetComponents(ComponentID, ComponentDescription)
SELECT @ComponentType, @CompDescr
FROM <table>
WHERE (AssetTagNumber = @TagNo)

Upvotes: 5

Related Questions