CODEWITHSUNDEEP
c#sqlsql-server
user3660473
user3660473

Reputation: 131

Working with composite primary key in c#

I have a sql table temp.it has 4 columns, 3 of which are primary keys(composite). While doing a insert to a table I need to check if the composite PK exists already in table. If yes I need to update the row else I need to insert a new row to temp table. Can I proceed this way. I don't know hoe to check for PK in table. Kindly guide me. Below is the Insert

  string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
            using (OdbcConnection con = new OdbcConnection(constr))
            {
                try
                {
                            string query = "Insert into temp_table(Name,DeptName,Alias,City) values(name,dept,alias,city)";
                            con.Open();
                            OdbcCommand cmd = new OdbcCommand(query, con);
                            cmd.ExecuteNonQuery();

              }

here name, dept and city are composite primary key.

Upvotes: 0

Views: 1219

Answers (3)

coding Bott
coding Bott

Reputation: 4357

The Merge Command combine check, insert and update into one command.

Syntax is here: https://msdn.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 0

GuidoG
GuidoG

Reputation: 12014

Try to update first, if the record does not exists than the update will fail and then you can do an insert.
This is more efficient because each time the update succeeds then only one statement will be called.

 update temp_table 
 set    Alias = @alias 
 where  Name = @name 
 and    DeptName = @dept

 if @@rowcount = 0 then
 begin
     insert into temp_table (Name, DeptName, Alias, City) 
     values (@name, @dept, @alias, @city)
 end

Upvotes: 1

Ravi
Ravi

Reputation: 1172

your solution

if not exists (Select * from temp_table where Name=@name and DeptName=@dept and City=@city)
begin
     Insert into temp_table
     (Name,DeptName,Alias,City) 
     values(@name,@dept,@alias,@city)
end
else
begin
     update temp_table set Alias=@alias where Name=@name and DeptName=@dept and City=@city
end

Upvotes: 1

Related Questions