INSERT Stored Procedure in SQL Server

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

and the Employee table

Insert Stored Procedure in SQL Server Example

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

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 sp.

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 SQL Server 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 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 the Employee table into the EmployeeDup table using the INSERT INTO SELECT Statement.

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 query

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

Let me execute the sp to load the values

EXEC [dbo].[InsertStoredProcedureSecondExample]
GO

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

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]

SQL Server 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.

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

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 Statements With Parameter in SQL Stored Procedure

This example shows you how to create Insert Stored procedures with parameters.

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 sp with the 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

Let us see the inserted data

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]

Insert Statements With Multiple Parameters in SQL Stored Procedure

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

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 sp 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

Use the below-shown query to check the inserted result.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]