How to Insert Data Using Stored Procedure in SQL Server

We can insert data into the database through different methods. One among them is the stored procedure.

In SQL Server, a procedure’s name, parameter lists, and Transact-SQL statements are always included. The stored procedures are the named objects in the SQL Database Server.

Applications like Java, Python, PHP, and others can call the procedures, as can triggers and other procedures. It supports almost all relational database systems.

Let’s dive deep into the insert data using the stored procedure in SQL Server.

Table of Contents

Stored Procedure in SQL Server

A stored procedure is a SQL code that you can save and reuse. Instead of writing the same query multiple times, you can call the stored procedure to execute the query.

Insert Data using Stored Procedure in SQL Server

Using the stored procedure, we can insert records into the table by passing data in input parameters. Below is the code to insert data in the HospitalInfo table using the stored procedure in SQL Server.

CREATE TABLE HospitalInfo
  (
HospitalID int,
Hospitalname  varchar(50),
Branchname varchar(50),
Totalstaffs char (20),
  )

Now, we can insert the values for the table created and see the below output.

Insert into HospitalInfo (HospitalID, Hospitalname, Branchname, Totalstaffs) 
                   values (422, 'Mayos clinic', 'Westcampus', 200)
                    values (354, 'Gilbert Hospital', 'saranac lake', 500)

Insert Multiple Rows into the Table using the Stored Procedure

Now, we will see how to insert multiple rows into the table using the stored procedure.

dbo – It is the schema name.
Employee_Insert – Procedure name.

Create Procedure dbo.Employee_Insert
As
Begin
select * from EmpDetails
End

To execute or run the stored procedure, use the below syntax.

Execute Employee_Insert

Insert data using stored procedure in SQL Server

Let’s try to insert multiple rows in the SQL server management studio.

CREATE PROCEDURE hospital_insertval
(
    @HospitalId int, 
    @HospitalName varchar(20), 
    @BranchName varchar(20), 
    @Totalstaffs int, 
    @Location varchar(50), 
    @PhysicianName varchar(50)
)
AS 
BEGIN
    INSERT INTO HospitalInfo (Hospitalid, Hospitalname, Branchname, Totalstaffs, Location, PhysicianName)
    VALUES (@HospitalId, @HospitalName, @BranchName, @Totalstaffs, @Location, @PhysicianName)
END;
 
EXEC hospital_insertval 403, 'Global', 'churchstreet', 120, 'German', 'David';

This is how we can add multiple rows into a table using stored procedure in SQL Server.

Insert data in a temp table using the stored procedure

Now, we will see how to insert data in a temporary table using the stored procedure in the SQL server.

Use the code below to create the table using a stored procedure and execute it. Then, insert data into a temporary table. # is the symbol for denoting a temporary table.

CREATE PROCEDURE InsertIntoTempTable
AS
BEGIN
CREATE TABLE #TempTable (
        ID INT,
        Name NVARCHAR(50),
        Age INT
    );

INSERT INTO #TempTable (ID, Name, Age)
    VALUES (1, 'David', 40),
           (2, 'peter', 65),
           (3, 'Daisy', 70);

		   SELECT * FROM #TempTable;
		   End

This is the method to create a temporary table using stored procedure in SQL Server.

Conclusion

This tutorial helps you learn to insert data into a table using a stored procedure. The stored procedure concept will be helpful if you want to reuse the code repeatedly. Try from your end and see the changes in SQL Server management studio.