INSERT Stored Procedure in SQL Server

How to write the INSERT Statements inside the SQL Stored Procedure?. Or How to write INSERT Stored Procedure in SQL Server with example. For this INSERT Stored Procedure demonstration, We are going to use the below table

INSERT Stored Procedure in SQL Server 1

and the Employee table

SQL Stored Procedures with Insert statement 0

Insert Stored Procedure in SQL Server Example

In this SQL Server example, we will show you how to use the SQL INSERT Statement inside the Stored procedure. I suggest you refer Introduction to Stored Procedures in SQL Server article to know the basics.

-- Example for INSERT Statement within the Stored Procedure in SQL Server
USE [SQL Tutorial]
GO

IF OBJECT_ID ( 'InsertStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFirstExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFirstExample

AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	VALUES ('Tutorial', 'Gateway', 'Education', 10000, 200)
          ,('Imran', 'Khan', 'Skilled Professional', 15900, 100)
          ,('Doe', 'Lara', 'Management', 15000, 60)
          ,('Ramesh', 'Kumar', 'Professional', 65000, 630)

END
GO

From the above code snippet, you can see we are inserting four rows into our empty EmployeeDup table. Run the above Insert Stored Procedure

Messages
--------
Command(s) completed successfully.

Let me use the EXEC Command (Execute Command) to execute the stored procedure to check the result

EXEC [dbo].[InsertStoredProcedureFirstExample]
GO

Execute the above query

Messages
--------
Command(s) completed successfully.

Now, let’s see whether the execution of the stored procedure inserted the new records into our EmployeeDup table or not

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
INSERT Stored Procedure in SQL Server 4

INSERT Stored Procedure in SQL Server Example 2

This example shows how to use the SELECT Statement and INSERT Statement inside the Stored procedure. From the below code snippet, you can see we are inserting all the records from Employee table into the EmployeeDup table using the INSERT INTO SELECT Statement

-- Example for INSERT Stored Procedure In SQL Server
IF OBJECT_ID ( 'InsertStoredProcedureSecondExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureSecondExample;  
GO

CREATE PROCEDURE InsertStoredProcedureSecondExample
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
					   FROM [Employee]
END
GO

Run the insert stored procedure query

Messages
--------
Command(s) completed successfully.

Let me execute the stored procedure to insert the values

EXEC [dbo].[InsertStoredProcedureSecondExample]
GO
INSERT Stored Procedure in SQL Server 6

Next, write down the following query to check whether we inserted the records or not.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
INSERT Stored Procedure in SQL Server 7

Insert Stored Procedure with Where Clause

In this example, we will show you how to use the WHERE Clause, along with the INSERT INTO SELECT Statement inside the Stored procedure. As you can see from the below code, the procedure should inset all the records from Employee to EmployeeDum whose Sales amount is greater than 3400.

-- Example for INSERT Statement inside the Stored Procedure in SQL Server
IF OBJECT_ID ( 'InsertStoredProcedureThirdExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureThirdExample;  
GO

CREATE PROCEDURE InsertStoredProcedureThirdExample
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
			   FROM [Employee]
			   WHERE [Sales] > 3400
END
GO

Run the above INSERT Stored Procedure with WHERE Clause query

Messages
--------
Command(s) completed successfully.

Before executing this stored procedure, We truncated the EmployeDum table to understand the records. Let us use the EXEC Command to execute the stored procedure

EXEC [dbo].[InsertStoredProcedureThirdExample]
GO
INSERT Stored Procedure in SQL Server 9

Let us run the following query and see whether the Stored Procedure inserted the records whose sales is greater than 3400 or not

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
INSERT Stored Procedure in SQL Server 10

Insert Statements With Parameter in SQL Stored Procedure

In this example, we show you how to create Insert Stored procedures with parameters.

-- Example for INSERT Statement inside the Stored Procedure in SQL Server
USE [SQL Tutorial]
GO

IF OBJECT_ID ( 'InsertStoredProcedureFourthExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFourthExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFourthExample
	@Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
			   FROM [Employee]
			   WHERE [Occupation] = @Occupation
END
GO

Run the above Insert Statements Stored Procedure with Parameters query

Messages
--------
Command(s) completed successfully.

Let me execute the stored procedure. As you can see, we had the @Occupation parameter. So, let us pass the parameter value using any of the following ways

EXEC [dbo].[InsertStoredProcedureFourthExample] N'Professional';
GO
--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFourthExample] N'Management';
GO
INSERT Stored Procedure in SQL Server 12

Let us see the inserted data

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
INSERT Stored Procedure in SQL Server 13

Insert Statements With Multiple Parameter in SQL Stored Procedure

In this example, we will use the Multiple parameters along with the Insert statement inside the stored procedure.

-- Example for INSERT Statement inside the Stored Procedure in SQL Server
IF OBJECT_ID ( 'InsertStoredProcedureFifthExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE InsertStoredProcedureFifthExample;  
GO

CREATE PROCEDURE InsertStoredProcedureFifthExample
	@Education VARCHAR(50),
	@Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [EmployeeDup] ([FirstName], [LastName], [Occupation], [YearlyIncome], [Sales])
	                   SELECT [FirstName], [LastName], [Occupation], [YearlyIncome], [Sales]
					   FROM [Employee]
					   WHERE [Education] = @Education OR [Occupation] = @Occupation
END
GO

Run the above Insert Statement Stored Procedure with multiple parameters query

Messages
--------
Command(s) completed successfully.

Following are the number of ways you can execute the stored procedure. Let me execute the sp.

EXEC [dbo].[InsertStoredProcedureFifthExample] @Education = N'Bachelors', @Occupation = N'Clerical';
GO
--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFifthExample] @Occupation = N'Clerical',  @Education = N'Bachelors';
GO

--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFifthExample] N'Bachelors', N'Clerical';
GO
INSERT Stored Procedure in SQL Server 15

Use the below shown query to check the inserted result.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
INSERT Stored Procedure in SQL Server 16