Inserting data into two tables using storeprocedure

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

hi ,i have two tables

Persons

id(pk)
firstname
lastname

and

Orders

orderNumber
orderdetails
id(fk)

I have to inserting data firstname,lastname,ordernumber,orderdetails using storeprocedure.Anyone pls help me.

Posted 28-Jul-12 10:01am

chinta123

Add a Solution

Comments

[no name] 28-Jul-12 16:07pm

Help with what? So write a stored procedure to insert that data into those two tables.

3 solutions

Solution 2

You need to create SP where you have two insert queries and one select query. Insert data in table 1 first. Post that, use the key created(you need to fetch it) for this new record inserted and insert a new record in table2.

Following couple of links will help you in learning on how to use SP:
ADO.NET : Using Stored Procedures[^]
MSDN: CREATE PROCEDURE (Transact-SQL)[^]

Posted 28-Jul-12 11:04am

Sandeep Mewara

Comments

Christian Graus 28-Jul-12 17:34pm

I don't think he needs a select ? A select for the id he inserted is not very reliable unless he knows the info he inserted is always unique. He needs @@IDENTITY.

Sandeep Mewara 29-Jul-12 3:07am

I agree Christian. I was talking of selecting the last inserted ID using @@Identity only. (My mistake, I missed mentioning that.)

Example: SELECT @Id=@@Identity

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)

Christian Graus · Accepted Answer · 2012-07-28T10:08:00

Solution 1

What is your question ? Your sproc can run any sql you like. It can do more than one statement. It can do more than one insert. Read here[^] to see how to get the last identity value that you inserted, I assume that is your core issue, but you don't say, you show no signs of having done research or thought about this, you just ask us for the answer.

Posted 28-Jul-12 10:08am

Christian Graus

Comments

chinta123 28-Jul-12 17:32pm

I think you have no idea regarding this.If you know the exact answer what i asked then tell me otherwise don't waste my tym..

Sergey Alexandrovich Kryukov 29-Jul-12 1:01am

OK, I got it. You simply have rotten morale. You repeat the same pattern as when I tried to answer your question.

First, you accuse the expert in having no idea on your topic, quite baseless accusation, when people comment on your posts, you play the offended card, and then you go straight rude. I don't know why you do it, perhaps this just how you feel.

You also apply double morale: you do not accept quite polite criticism against you, but think that you have an every right to say offending words (and even obscene words, as I can see).

I do understand that any advice to re-think your life is useless (even though it could greatly help you). I just note that with such attitude you cannot be welcome here; and probably anywhere else. Sorry for you. No, no need to thank me... :-)

Good luck,
--SA

chinta123 29-Jul-12 1:27am

hello,I got my answer .you first tell your expert how to talk people,and you don't worry about my attitude.2nd thing i am just a beginner in .net.So may be my question easy for you,but if you have no interest to give my answer then don't . I am not forcing you.,and 2nd thing who r u to tell me u r not welcome here ,first you change your attitide then tell me.

Christian Graus 29-Jul-12 1:29am

Please don't keep brawling with people. If you're too much of a beginner to be able to read articles, and if you need us to give you the code, then you're not a beginner, but a leech. If you want to learn programming, choose an entry point where you can read documentation and understand it. That's the core issue here. A 'beginner' should not be doing what you're doing ( and doubtless charging for it ), you should be working through a beginners book and LEARNING, not just posting questions here and hoping for code you can paste, but don't understand.

chinta123 29-Jul-12 1:41am

It's sunday .I have many work .I am not in a mood to talk with you.better you shut up..and if you want the code then see..

Collapse | Copy Code

Create proc asp_insert_Persons_order
(
@FirstName varchar(100),
@LastName varchar(100),
@OrderNumber int,
@OrderDetails varchar(100)
)
AS
BEgin

Begin tran
Declare @Id int
Insert into Persons(FirstName, LastName ) values (@FirstName,@LastName )
Select @Id=@@Identity

Insert into Orders values (@OrderNumber ,@OrderDetails ,@Id)

Commit tran
END

Christian Graus 29-Jul-12 1:44am

That's the code someone else gave you, I am more of your friend than they are. That line 'Select @Id=@@Identity' - do you have any clue what it does ? It is the line that is documented in the link I sent to you. If you'd read it, you could have learned how to write this code, it's exactly the code I was trying to show you how to write. I just prefer to help programmers learn, than do the work of lazy people. If you'd just applied yourself, you'd be able to write code like this in the future, but I strongly suspect the next time you need to do this, you will post here again because you could not understand the link I gave you and spent the day playing games while you waited for someone to do the work for you.

Sergey Alexandrovich Kryukov 29-Jul-12 15:44pm

Ha-ha!
Well, good luck...
--SA

Christian Graus 28-Jul-12 17:33pm

I am sorry, but if you can't understand my answer, then you're wasting OUR time. I told you exactly what you need to do. I just didn't do your job for you. Read the article I posted, and try to learn instead of asking us to do all the work for you. If you try to apply this and get stuck, ask another question, where you show us what you did and where you are stuck. I am happy to help people learning. I have my own job though, and I have no interest in doing yours if you can't be bothered trying to do it.

chinta123 28-Jul-12 17:39pm

k.you better do your job.Don't interfere if you have no idea.

Christian Graus 28-Jul-12 17:41pm

Here's a suggestion. The code you want to write is trivial to me, I write more complex SQL, daily. So, I have a LOT of ideas. But, if you're doing homework or if you're ripping someone off by pretending to be a programmer who can do paid work, the fact you're asking here, means you're the one who is incapable of teaching yourself, using google, etc. As such, your reliance on our kindness in being willing to do what you're not smart enough to work out, does not put you in a position to be abusive.

chinta123 28-Jul-12 17:49pm

I know what you are and what you can do...don't waste your tym.Why r u writing debate like this.I believe in work not in words ..I saw your profile and comments also..You are their for writing debate :D

Sergey Alexandrovich Kryukov 29-Jul-12 1:03am

Do not lie, it cannot help. Do not lie to yourself first of all. Double standard, again...
--SA

Christian Graus 28-Jul-12 18:05pm

It is sunday morning here. A perfect time for amusing myself with a moron who wants to pick fights with the people he relies on because he is incompetent at his job. Yes, I tell non programmers they need to ask clear questions or learn to program. But, I trawl through idiots like you to find people who want to learn, then I will go back for days, and keep helping people who want to learn. I gave you a clear and honest answer. If it was not enough, you could have asked for more detail. Instead, you attacked me, to punish me for your ignorance. You're a fool.

chinta123 28-Jul-12 18:19pm

you don't worry .i am not going to attack you.Enjoy your day...:)

Mohamed Mitwalli 29-Jul-12 2:42am

5+

Santhosh Kumar Jayaraman · Accepted Answer · 2012-07-28T15:09:00

SQL

Create proc asp_insert_Persons_order
(
@FirstName varchar(100),
@LastName varchar(100),
@OrderNumber int,
@OrderDetails varchar(100)
)
AS
BEgin

Begin tran
Declare @Id int
 Insert into Persons(FirstName, LastName ) values (@FirstName,@LastName )
Select @Id=@@Identity

Insert into Orders values (@OrderNumber ,@OrderDetails ,@Id)

Commit tran
END

Inserting data into two tables using storeprocedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us