Insert multiple records using stored procedure

Question

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 employeeid . 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?

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

What do you mean by multiple student records? What would be the value of `st_name` for these multiple records? Show us how you're calling the SP. — Felix Pamittan, Apr 30 '15 at 08:47
is it possible to add one primary key use insert more than one foriegnkey — Unni R, Apr 30 '15 at 09:02

Edward N · Accepted Answer · 2015-05-05T06:57:12.017

For your case, you can try this code above ( I'm using XML parameter type)

CREATE PROCEDURE EmployeeIns
  @EmployeeName NVARCHAR(50),
  @Students XML
AS
/*
  @Students : <Students>
                  <Student Name='Studen 1'/>
                  <Student Name='Studen 1'/>
              </Students>
*/
BEGIN
    DECLARE @StudenTable TABLE(Name NVARCHAR(50))
    DECLARE @EmployeeId INT

    INSERT INTO @StudenTable
    SELECT Tbl.Col.value('@Name', 'NVARCHAR(50)')
    FROM @Students.nodes('//Student') Tbl(Col)

    INSERT INTO Emplyoee VALUES(@EmployeeName)
    SET @EmployeeId = SCOPE_IDENTITY()

    INSERT INTO Student 
    SELECT @EmployeeId, Name FROM @StudenTable
END

Update 1 : Your table design should be look like this :

CREATE TABLE [dbo].[Emplyoee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](150) NULL,
 CONSTRAINT [PK_Emplyoee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))

CREATE TABLE [dbo].[Student](
    [EmployeeId] [int] NULL,
    [Name] [nvarchar](150) NULL,
    [Id] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))

The execute code :

EXEC EmployeeIns @EmployeeName='trungtin1710', @Students = '<Students><Student Name="Studen 1"/><Student Name="Studen 1"/></Students>'

@UnniR pls check update 1, you should change the schema of your Student table — Edward N, May 05 '15 at 06:58
@UnniR you should search around the internet, there are a lot of topic for this. For example : http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program — Edward N, May 06 '15 at 07:34

Roman Marusyk · Answer 2 · 2015-04-30T09:46:07.713

0

As I understand: If emplyoee with @emp_name is already exists then insert student records with ID of the emplyoee, if there is not any emplyoee with @emp_name then need to insert new emplyoee and student with ID of the new emplyoee. Yes?

CREATE PROCEDURE empst_Sp
@emp_name varchar(50),
@st_name varchar(50)
AS
BEGIN    
    DECLARE @EmplyoeeId  int
    SET @EmplyoeeId = NULL

    select @EmplyoeeId = id 
    from   emplyoee 
    where  emp_name = @emp_name

    IF @EmplyoeeId IS NULL
    BEGIN
      insert into emplyoee (emp_name) values (@emp_name)
      SET @EmplyoeeId = SCOPE_IDENTITY()
    END

    insert into student(id, st_name) values(@EmplyoeeId, @st_name)
END

edited Apr 30 '15 at 09:46

answered Apr 30 '15 at 09:03

Roman Marusyk

23,328
24
73
116

Yes, same id (Employee)use to insert more than student. – Unni R Apr 30 '15 at 09:10
Ok, please try this code. The code need to refactor for more beautiful view if it work – Roman Marusyk Apr 30 '15 at 09:24
I want to insert at a time more than one value in student table using primary key . – Unni R May 05 '15 at 04:33

score 0 · Answer 3 · answered Apr 30 '15 at 09:05

All you need is a local variable in which you can set the value retrieved from Scope_Identity:-

CREATE PROCEDURE empst_Sp
@emp_name varchar(50),
@st_name varchar(50)
AS
BEGIN
DECLARE @id INT
INSERT INTO emplyoee (emp_name) VALUES (@emp_name)
set @id = SCOPE_IDENTITY()
INSERT INTO student(id,st_name) VALUES (@id,@st_name)
END

Insert multiple records using stored procedure

3 Answers3