SQL Server stored procedure insert into with examples

In this article, we will discuss about the SQL Server stored procedure insert into with a few examples.

Stored Procedures become handy when you have a SQL code that you need to repeat a lot. A stored procedure is a set of SQL code specifically written for performing a task. We can write a stored procedure and execute it with a single line of SQL code.

One of the tasks that you can perform with the stored procedures is to insert rows in a table. You do not need to write every time the full syntax of the insert statement. Instead, you can create a stored procedure once and execute it with a single line.

You will see a number of examples of an insert stored procedure in this article. These examples are mentioned in the below list:

• SQL Server stored procedure insert into table
• SQL Server stored procedure insert into temp table
• SQL Server stored procedure insert into table return id
• SQL Server stored procedure insert into select
• SQL Server stored procedure insert into multiple tables
• SQL Server stored procedure insert into variable
• SQL Server insert stored procedure results into table
• SQL Server insert stored procedure results into table variable

SQL Server stored procedure insert into table

In this section, you will learn to create a stored procedure that will insert a row into a table in SQL Server 2019. You will see the examples that we have created for the demonstration purpose.

• Let us create a table. I will create a table where we will store the states name along with their population, of the Unites States of America.

USE BackupDatabase
GO
CREATE TABLE dbo.States_in_USA(
	[Sr. No] int IDENTITY(1,1),
	[State] nvarchar(30),
	[Population] int
)

• Now we will create a stored procedure using which we can insert the data into the table.

USE BackupDatabase
GO
CREATE PROCEDURE dbo.InsertInto @state nvarchar(30), @population int
AS
BEGIN
	INSERT INTO dbo.States_in_USA(
		State, Population)
		VALUES(@state, @population)
END

• We have defined two parameters in the stored procedure:
  • @state: Name of a state in USA
  • @population: Population of the provided state
• The stored procedure will take these values as input and use these inside an insert statement to insert the record in the dbo.States_in_USA table.
• Now we will execute this stored procedure with some values:

USE BackupDatabase
GO
EXEC dbo.InsertInto 'California' , 39613493
SELECT * FROM dbo.States_in_USA

You can see in the output that the values that we supplied to the stored procedure got inserted into the table. In this way, you can create a stored procedure that will insert some data into a table.

Now let us see one more example.

• We have a table named Department storing the department details of a company.

• We will create a stored procedure to insert a record in the Department table.

USE master
GO
CREATE PROCEDURE dbo.InsertDepartment @ID int, @Name nchar(20)
AS
BEGIN
	INSERT INTO dbo.Department(
		DepID, DepName)
		VALUES(@ID, @Name)
END

• The above stored procedure will take two input parameters:
  • @ID: The Department ID
  • @Name: The department name corresponding to the department ID.

• The stored procedure will take these values as input and use these inside an insert statement to insert the record in the Department table.
• Now let us execute this stored procedure and see if it inserts the values as a record.

USE master
GO
EXEC dbo.InsertDepartment 16, 'R&D'
SELECT * FROM dbo.Department

• The row is successfully inserted.

Thus, you might have learned how you can insert a row into a table using a stored procedure.

Read SQL Server trigger after insert with examples

SQL Server stored procedure insert into temp table

In this section, you will learn how you can insert a row into a SQL server temp table using a stored procedure. We will discuss this with an example.

• Firstly, let us create a temp table in sql server.

USE tempdb
GO
DROP TABLE IF EXISTS dbo.#tempTable
CREATE TABLE dbo.#tempTable(
[Student ID] int,
[Student Name] nchar(20)
)

• Now create a stored procedure that will insert a record in the temp table.
• The procedure has two input parameters.

CREATE PROCEDURE InsertTemp @ID int, @Name nchar(20)
AS
BEGIN
	INSERT INTO dbo.#tempTable([Student ID], [Student Name])
	VALUES(@ID, @Name)
END

• Let us execute the stored procedure with some input values:

EXEC [tempdb].[dbo].[InsertTemp] 1231, 'Bruno'
SELECT * FROM [tempdb].[dbo].[#tempTable]

• You can see in the output that the row is inserted.

Important Note: Execute the stored procedure in the same query window in which you have created the temp table if you are using SQL Server Management Studio. Executing the stored procedure in another window will throw an error and the row will not be inserted into the table.

SQL Server stored procedure insert into table return id

You can return the last inserted identity value from a table using the SCOPE_IDENTITY() function. When you insert a row in a table, you can use the SCOPE_IDENTITY function to fetch the last inserted identity column value.

In this section, we will create an example, where we will create a stored procedure that will insert a record in a table having an identity column. After inserting the record, it will also return the last inserted identity column through an output parameter.

• Consider the table TableIdentity.

• Let us create a stored procedure that will insert a row in the table and return the last inserted identity column value.

USE master
GO
CREATE PROCEDURE dbo.ReturnID @EmpName nchar(20), @EmpDep nchar(10),
	@Identity int OUTPUT
AS
BEGIN
	INSERT INTO dbo.TableIdentity(EmpName, EmpDep)
	VALUES( @EmpName, @EmpDep)
	SET @Identity= SCOPE_IDENTITY()
END

• Now we will execute this stored procedure.

USE master
GO
DECLARE
	@IDValue int
EXEC dbo.ReturnID 'Jimmy', 'Finance', @IDvalue OUTPUT
PRINT(@IDvalue) 

• The stored procedure returned 20 as the last inserted identity value.
• Let us verify if the same value was inserted in the table.

• As you can see in the output, the stored procedure has returned the last inserted identity column value.

Thus, you might have learned how you can use the SCOPE_IDENTITY function to return the last inserted identity column value.

Read SQL Server stored procedure naming convention and best practices

SQL Server stored procedure insert into select

In this section, you will learn to use the INSERT INTO SELECT statement within a stored procedure. You can use this statement to copy data from one table to another.

We will create an example of a stored procedure that will copy data from one table to another using the INSERT INTO SELECT statement.

• Consider the following SalesTable table. We will create a new table named NewSales with the same structure and copy the data from the SalesTable.

• Firstly, we will create the table NewSales.

USE [master]
GO
DROP TABLE IF EXISTS dbo.NewSales
CREATE TABLE [dbo].[NewSales](
	[CustomerID] [int] NOT NULL,
	[Name] [nchar](20) NOT NULL,
	[ProductID] [nchar](20) NULL,
	[ProductName] [nchar](30) NOT NULL
) ON [PRIMARY]
GO

• Once we have created the table, we can create a stored procedure that will copy the data from the SalesTable table to the NewSales table.

USE master
GO
CREATE PROCEDURE dbo.InsertSelect
AS
BEGIN
	INSERT INTO dbo.NewSales(
	[CustomerID], [Name], [ProductID], [ProductName])
	SELECT [CustomerID], [Name], [ProductID], [ProductName] FROM dbo.SalesTable
END

• Now let us execute the stored procedure and check if the data is copied or not.

USE master
GO
EXEC dbo.InsertSelect
SELECT * FROM dbo.NewSales

Thus, with this example, you might have learned how you can use the INSERT INTO SELECT statement to copy data from one table to another using a stored procedure.

SQL Server stored procedure insert into multiple tables

In this section, you will learn how to create a stored procedure that inserts records into multiple tables in SQL Server 2019.

• Consider the two tables in sql server:
  • Persons: Storing personal details of people
  • Phones: Storing only the phone numbers and names of the people in the Persons table.

• We will create a stored procedure that will insert the records in both th tables at a single execution.

USE master
GO
CREATE PROCEDURE dbo.InsertMultiple @FirstName nchar(10),
	@LastName nchar(10), @Age smallint, @Gender nchar(7),
	@Email nchar(30), @Phone nchar(20), @Location nchar(20)
AS
BEGIN
	INSERT INTO dbo.Persons(
	[First Name], [Last Name], Age, Gender, Email, Phone, Location)
	VALUES(@FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
	INSERT INTO dbo.Phones(
	[Phone], [First Name], [Last Name])
	VALUES( @Phone, @FirstName, @LastName)
END

• We will pass the values that we want to insert into the tables as the input parameters to the stored procedure.
• Once the stored procedure is created, we can execute it with the EXEC statement.

USE master
GO
EXEC dbo.InsertMultiple 'Selena', 'Gomez', 34, 'Female', 'selena123@gmail.com', '+1 701 756 783', 'Medora'

• As a result, the records are inserted in both the tables.

Hence, you might have learned how to insert records into multiple tables using a stored procedure.

Read SQL Server stored procedure if else

SQL Server stored procedure insert into variable

In this section, you will learn how to insert the output value of a stored procedure into a variable in SQL server 2019.

• Firstly, declare the variable.
• Then use this variable as the output parameter in the stored procedure.
• Let us understand this with an example.
• Consider the following Employees table.

• We will create a stored procedure with an output parameter. We will use the output parameter to store the value into a variable.

USE master
GO
CREATE PROCEDURE dbo.InsertIntoVariable @EmpID int, @EmpName nchar(20) OUTPUT
AS
BEGIN
	SELECT @EmpName= EmpName FROM dbo.Employees
	WHERE EmpID= @EmpID
END

• The above created stored procedure will take the Employee ID as the input parameter and it will return the Employee Name as the output parameter from the Employees table.
• Now we will declare the variable into which we want to store the value and execute the stored procedure.

USE master
GO
DECLARE
@EmpName nchar(20)
EXEC dbo.InsertIntoVariable 1001, @EmpName OUTPUT
PRINT(@EmpName)

• You can see that the value is inserted into the variable and printed on the output screen.

Thus, you might have learned how to insert a value returned from a stored procedure into a variable.

Read IDENTITY_INSERT in SQL Server

SQL Server insert stored procedure results into table

In this section, you will learn how to insert the results of a stored procedure into a table in SQL Server.

There are two types of values you would want to store in the table from the stored procedure results:

• Table Values
• Scalar Values

Insert table values from stored procedure results into a table:

In a case when a stored procedure returns a resultset, you can insert that resultset into a table. In this section, you will an example where we will store the resultset returned by the stored procedure into a table.

• We have a table named Student.

• We will create a stored procedure that will retrieve some column values of all the records from the Student table and store it into another table named StudentID.

USE master
GO
CREATE PROCEDURE dbo.InsertStudent
AS
BEGIN
	SELECT [First Name], [Last Name], [College ID]
	FROM dbo.Student
END

• Initially, the StudentID table is empty. We will insert values into this table by executing the stored procedure.

USE master
GO
INSERT INTO dbo.StudentID
EXEC dbo.InsertStudent
GO
SELECT * FROM StudentID

• The specified column values are stored in the StudentID table.

This is the way how you store the table values from a stored procedure into a table.

Insert scalar values from stored procedure results into a table:

If a stored procedure returns a scalar value, you can insert that value into a table. We will show a demo of this task. We will use output parameters to return a scalar value from a stored procedure.

• We have created a table named dbo.Sumtable that has three columns:
  • First Number
  • Second Number
  • Sum of Numbers
• We will give two numbers as an input to a stored procedure and the stored procedure will calculate the sum and return the sum as the output parameter.
• Later, the two numbers and the sum will be stored into Sumtable.
• We created the stored procedure as:

USE [master]
GO

CREATE PROCEDURE [dbo].[addition] @first_number real, @second_number real,
	@SumNum real OUTPUT
AS
BEGIN
SET @SumNum= @first_number+@second_number
END

• Now we will create variables to be passed as paramaters to the stored procedure.
• Then we will insert the values into the Sumtable.

USE master
GO
DECLARE
	@FirstNum real,
	@SecondNum real,
	@Sum real
SET @FirstNum= 500
SET @SecondNum= 1000
EXEC dbo.addition @FirstNum, @SecondNum, @Sum OUTPUT
INSERT INTO dbo.Sumtable(
	[First Number], [Second Number], [Sum of Numbers])
	VALUES( @FirstNum, @SecondNum, @Sum)
GO
SELECT * FROM dbo.Sumtable

• The output value returned from the stored procedure is stored in the @Sum and then passed to the Insert statement.

In this way, you can store a scalar value result of a stored procedure into a table.

Read PostgreSQL ADD COLUMN + 17 Examples

SQL Server insert stored procedure results into table variable

In this section, you will learn how to store the results of a stored procedure in a table variable in SQL Server. We will create an example to understand this concept.

• Consider the following Products table.

• Firstly, we will create a stored procedure that will retrieve records from the Products table.
• The stored procedure will take a Product ID as input parameter and retrieve the record corresponding to the Product ID.

USE master
GO
CREATE PROCEDURE dbo.InsertVariable @ProductID int
AS
BEGIN
	SELECT * FROM dbo.Products WHERE [Product ID]= @ProductID
END

• Now we will create a table variable named @TableVar into which we want to store the results.
• Then we will execute the stored procedure with a product ID.

USE master
GO
DECLARE
@TableVar TABLE(
ProductID int,
Name nchar(30),
Price real,
Rating int)

INSERT INTO @TableVar
EXEC dbo.InsertVariable 1238

SELECT * FROM @TableVar

• The values got stored in the table variable.

Thus, you might have learned how to insert the results of a stored procedure into a table variable.

You may like the following database articles:

• PostgreSQL DATE Functions with Examples
• msg 3609 the transaction ended in the trigger
• PostgreSQL ADD COLUMN + 17 Examples
• How to view stored procedure in SQL Server
• SQL Server move database files
• Msg 11555 NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions

In this tutorial, we learned about SQL Server stored procedure insert into table with a few examples.

• SQL Server stored procedure insert into table
• SQL Server stored procedure insert into temp table
• SQL Server stored procedure insert into table return id
• SQL Server stored procedure insert into select
• SQL Server stored procedure insert into multiple tables
• SQL Server stored procedure insert into variable
• SQL Server insert stored procedure results into table
• SQL Server insert stored procedure results into table variable