CODEWITHSUNDEEP
c#sqlsqlcommand
Junx197
Junx197

Reputation: 13

C# sqlcommand If not exist code error

    public void Test2()
    {
        string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            con.Open();
            SqlCommand com = new SqlCommand("select * from Temp_Student", con);
            SqlDataReader reader = com.ExecuteReader();

            while (reader.Read())
            {
                string sql = "IF NOT EXISTS (SELECT s_name FROM Student WHERE s_name = @chp1) BEGIN INSERT INTO Student(s_name, s_pass) values(@chp1, @chp2) END";

                string test = "";

                SqlCommand com1 = new SqlCommand(sql, con);
                com1.Parameters.AddWithValue("@chp1", test);
                com1.Parameters.AddWithValue("@chp2", test);
                com1.ExecuteNonQuery();

            }
            con.Close();
        }
    }

Currently i am trying to insert data from Temp_student table into Student table, with the exception of duplicates which already exists in Student table. With the current code i don't run into any errors but on my Student table, there is a row of blank values inserted even though currently there is no duplicates.

What i wanted to do was to insert 2 columns from Temp_Student into Students without duplication and currently this is a part of the code where i was passed by another. i do not really understand much of this. If there are other ways please suggest to me too. Thank you

Upvotes: 0

Views: 373

Answers (1)

Scott Hannen
Scott Hannen

Reputation: 29252

I don't know the exact columns in your table, but you can do this (adjusting for actual columns), assuming that Student_Name is your primary key.

insert into Student
select 
    tmp.Student_Name, tmp.Student_Pass
from
    Temp_Student tmp
    outer join Student stn on tmp.Student_Name = stn.Student_Name
where stn.Student_Name is null

The outer join returns all records in Temp_Student whether or not there are matching records in Student. When there are no matching records in Student the columns selected from that table are null. So by selecting based on this join we return only records in Temp_Student that aren't matched in Student.

You can also use a MERGE statement. A lot of people who were writing SQL before 2008 got used to doing it the "old" way but this is more explicit.

MERGE Students AS T
USING Temp_Students AS S
ON (T.Student_Name= S.Student_Name) 
WHEN NOT MATCHED BY TARGET  
    THEN INSERT(Student_Name, Student_Pass) VALUES(S.Student_Name, S.Student_Pass);

MERGE must be terminated with a semicolon. Technically all statements should be but SQL enforces it on MERGE.

Upvotes: 1

Related Questions