Insert multiple records using stored procedure

Question

3.43/5 (3 votes)

See more:

SQL-server-2000

, +

I have two tables

emplyoee (first table)
id primary key auto increment
emp_name varchar

student(second table)
id foriegnkey emplyoee.id
st_name varchar

i want to insert multiple student records for a single employe id . My code is attached here , but this use to only one student record update.how can i write stored procedure for this need.
i am new with SQL server and stored procedure , could you please help me.

SQL

create procedure  empst_Sp
@emp_name varchar(50),
@st_name varchar(50)
as
begin
insert into emplyoee (emp_name) values (@emp_name)
insert into student(id,st_name) values(SCOPE_IDENTITY(),@st_name)
end

Posted 29-Apr-15 18:28pm

Unni R

Updated 29-Apr-15 18:54pm

Suvendu Shekhar Giri

v3

Add a Solution

Comments

jgakenhe 30-Apr-15 0:51am

It is possible to send in multiple records into a stored procedure, but it is hard and this is not what you want to do.

You want to insert 1 record at a time, so if there is a problem you can roll it back (http://en.wikipedia.org/wiki/ACID). My suggestion to you is that if you are calling this from a web page, then when the user clicks submit, you gather the data and For Each record do a single insert. For instance if Student A wants to enroll in 2 classes, when they click submit, your code gets the values and For Each of these classes, it does 1 execution of your store procedure above. Your stored procedure should work just fine.

upendra shahi 30-Apr-15 1:20am

nice question..

2 solutions

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)

Manoj Kumar Mandal · Answer 1 · 2015-04-29T19:41:00

Hi,

I think you have a data table for the list which you to save along with only one employee id.

You can check this link It is about how you can send datatable to stored procedure.

you can use help from that article and send employee details as simple parameter and student details as specified in the article(as XMl file).

eddiecjc · Answer 2 · 2015-04-29T19:57:00

Not sure if all the syntax below works with SQL2000.
The solution is to pass a list of Student Names as XML into the Stored Procedure, and update the INSERT statement to insert multiple records.

SQL

DECLARE @students TABLE (st_name VARCHAR(50));
DECLARE @students_xml XML;
INSERT @students
    SELECT 'Michael'
    UNION
    SELECT 'Bob'

-- Convert table into an XML
SET @students_xml =
    (
        SELECT *
        FROM @students AS student
        FOR XML AUTO, ROOT('students')
    );

-- Parse the XML to a table
SELECT
    Tbl.Col.value('@st_name', 'varchar(50)') AS st_name
FROM @students_xml.nodes('//students/student') Tbl(Col)

Also, if you are using SQL 2008 and above, you can use Table-Valued parameter.

Insert multiple records using stored procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0