how to insert and update data into two tables using Stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 9-Jun-15 5:04am

Member 11074153

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ZurdoDev · Answer 1 · 2015-06-09T05:33:00

Solution 1

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

Posted 9-Jun-15 5:33am

ZurdoDev

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?