Click here to Skip to main content
15,882,412 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Table 1: loginID(PK), name
Table 2: loginID(FK), UserRole, agency

How can we insert and update data into both tables using 1 stored procedure?

condition: if first time we are creating new loginID, then it should allow us to insert data directly but if loginID already exists then it should update the data into both table based on loginID.

Please suggest on the same.
Thanks in advance.
Posted
Comments
F-ES Sitecore 9-Jun-15 11:36am    
Use "IF" statements to see what work needs done, and to check if data already exists in a table use "EXITS". google for "t-sql if statement" and "t-sql exists" for examples of syntax.

IF EXISTS (...)
UPDATE ...
ELSE
INSERT INTO ....

1 solution

This is much easier than you think. Do something like the following:

SQL
IF EXISTS(SELECT * FROM Table1 WHERE loginID = @loginID)
  BEGIN
    -- record exists so do update
    UPDATE table1 SET field1 = @value1
    UPDATE table2 SET field1 = @value1
  END
ELSE 
  BEGIN 
    -- record does not exist so do insert
    INSERT INTO TABLE1(field1, field2)
    VALUES (@field1, @field2)
  END
 
Share this answer
 
Comments
Member 11074153 9-Jun-15 14:08pm    
Thank you for your response.
It is inserting data into TABLE1 only.Please confirm what about second table.
how we would insert/update in that in second table, as it is having FK for TABLE1.
ZurdoDev 9-Jun-15 15:15pm    
What is the key in table1? Is it an identity field?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900