Stored procedure in SQL Server for insert and update

In this SQL Server tutorial, we will study how to create a stored procedure in SQL Server for insert and update operations. Additionally, we will also discuss the following set of topics in this tutorial.

• Stored procedure for insert and update in SQL Server
• Stored procedure in SQL Server for inserting data into a table
• Stored procedure in SQL Server to insert into table
• Stored procedure SQL Server insert date
• Insert stored procedure in SQL Server with parameters
• Insert stored procedure in SQL Server example
• Stored procedure SQL Server bulk insert
• Stored procedure SQL Server select insert
• Stored procedure in SQL Server for insert update delete
• Stored procedure SQL Server update table
• Update stored procedure in SQL Server example
• Stored procedure for bulk update in SQL Server

Stored procedure for insert and update in SQL Server

In this section, we will learn how to create a stored procedure in SQL Server for an insert and update operation at once.

A stored procedure in SQL Server is a set of SQL queries that you can use again and again. You can create your own set of SQL queries and then store it as a procedure. You can execute that stored procedure by just calling it with a single line of code.

To understand this topic better, we will illustrate an example. In the example, we will create a stored procedure that will either insert or update a record based upon its existence.

Firstly, we will create a sample table in SQL Server and insert some records in it. Here is the query for this task.

USE [sqlserverguides]
GO

CREATE TABLE Customertbl (
	cust_id INT IDENTITY (1,1) NOT NULL,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(50),
	city VARCHAR(50),
	country VARCHAR(50)
);
INSERT INTO CustomerTbl (first_name, last_name, email, city, country) VALUES ('Harold', 'Seggie', 'hseggie0@techcrunch.com', 'Toronto', 'Canada');
INSERT INTO CustomerTbl (first_name, last_name, email, city, country) VALUES ('Kial', 'Seeds', 'kseeds1@mlb.com', 'Auckland', 'New Zealand');
INSERT INTO CustomerTbl (first_name, last_name, email, city, country) VALUES ('Rebekkah', 'Lambal', 'rlambal2@webnode.com', 'New York', 'United States');
INSERT INTO CustomerTbl (first_name, last_name, email, city, country) VALUES ('Risa', 'Fayer', 'rfayer3@so-net.ne.jp', 'Montreal', 'Canada');
INSERT INTO CustomerTbl (first_name, last_name, email, city, country) VALUES ('Kippy', 'Bawden', 'kbawden4@nyu.edu', 'Chicago', 'United States');

In the above example, we have created a table with the name Customertbl. Moreover, er have also inserted 5 records in the table. And if we query the table, we will get the following result.

Next, we have to create a stored procedure in SQL Server that will perform the following operation.

• First, it will check the existence of a record in the Customertbl
• Now, if the record already exists then, it will use the input values to update that record.
• However, if the record does not exist then it will use the input values to insert that record.

USE [sqlserverguides]
GO

CREATE PROCEDURE usp_InsertUpdateData
(
    @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @Email VARCHAR(50),
    @City VARCHAR(50),
    @Country VARCHAR(50)
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM [Customertbl]
    WHERE email=@Email
	)
    BEGIN
        UPDATE [Customertbl] SET 
	       first_name = @first_name,
	       last_name = @last_name,
	       city = @City,
               country = @Country
	       WHERE Email = @Email
	PRINT 'Customer Data Updated'
    END
ELSE
    BEGIN
        INSERT INTO [Customertbl] VALUES (@first_name, @last_name, @Email, @City, @Country)
	PRINT 'New Customer Data Inserted'
    END
END

In the above procedure, we are creating a stored procedure that accepts 5 input parameters. And first, we are checking the existence of the record using the email parameter. Moreover, based upon the existence, we are running the update or insert statement.

Furthermore, the procedure will return a print statement based upon the action performed by the stored procedure.

Execution of stored procedure

Let’s try to execute the above procedure by providing an existing email address and updating the country field. And also try to execute the procedure by providing a new record.

USE [sqlserverguides]
GO

EXEC usp_InsertUpdateData 'Kippy', 'Bawden', 'kbawden4@nyu.edu', 'Toronto', 'Canada'

EXEC usp_InsertUpdateData 'Sherie', 'Lumbers', 'slumbers1@harvard.edu', 'Chicago', 'United States'

In the above example, first, we are executing the procedure by providing an existing email and changing the city and country fields.

After this, we are executing the procedure by providing new values. In the end, we will get the following output.

Also, check: SQL Server logical operators and example

Stored procedure SQL Server insert date

In this section, we will discuss how to create a stored procedure in SQL Server to insert date values in a table. And for this task, first, we will create a simple table in the database.

USE [sqlserverguides]
GO

CREATE TABLE Ordertbl (
	order_id INT IDENTITY (1,1) NOT NULL,
	product_name VARCHAR(50),
	order_date DATE
);

In the above query, we have created a new table in the sqlserverguides database. And the name of this table is Ordertbl and it holds 3 columns order_id, proceduect_name, and order_date.

Next, we will create a stored procedure in SQL Server to insert a new record in this table including date value.

CREATE PROCEDURE usp_InsertOrderData
(
    @Product VARCHAR(50)
)
AS
BEGIN
    DECLARE @OrderDate DATE
    SET @OrderDate = GETDATE() 
    INSERT INTO [Ordertbl] VALUES (@Product, @OrderDate)
    SELECT * FROM [Ordertbl]
END

• In the above query, we have created a stored procedure usp_InsertOrderData. Now, this procedure accepts the product name as an input parameter.
• And in the body of the procedure, first, we declared an order date variable. And then, using the GETDATE function to assign the current date value to the OrderDate variable.
• After this, we are using the INSERT statement to insert the product name and order date values in the table. In the last, we are also using the SELECT statement to fetch the table records.

Now, let’s use this procedure to make some entries in the Ordertbl.

USE [sqlserverguides]
GO

EXEC usp_InsertOrderData 'American Cheese'

In the above query, we are executing the stored procedure and we also provided the product name as American Cheese. Here is the final output.

Read: SQL Server stored procedure if exists update else insert

Stored procedure SQL Server bulk insert

Bulk insert in SQL Server is a technique to move a large amount of data from a source to a new destination. Moreover, there are various ways to bulk insert data from a CSV file into a SQL Server database.

Therefore, we can use the SQL Server Import and Export Wizard, BCP utility, or the BULK INSERT statement. So, in this tutorial, we understand how to create a stored procedure in SQL Server for the bulk insert operation.

Let’s, understand this implementation using an example in SQL Server. And for this, we will use the Customertbl table from the first section. Also, we will bulk insert the following data from CSV into the table.

Next, we will create a stored procedure in SQL Server that will bulk insert the data from the Customer_Data.csv file into the Customertbl. Moreover, you can also download the CSV file from the following download link.

The SQL script to create a stored procedure is as follows.

USE [sqlserverguides]
GO

CREATE PROC usp_bulkInsertCustomers
AS
BEGIN
  BULK INSERT dbo.Customertbl
  FROM 'C:\Users\Shivam\Downloads\Customer_Data.csv'
  WITH (FORMAT = 'CSV'
        , FIELDTERMINATOR = ','
        , ROWTERMINATOR = '0x0a');
END

In the above query, we are creating a simple stored procedure that uses the BULK INSERT statement to insert data from given CSV to Customertbl.

USE [sqlserverguides]
GO

EXEC usp_bulkInsertCustomers

SELECT * FROM Customertbl

Now, when we execute the above procedure and query the table, we will get the following result.

Also, check: SQL Server bulk insert from CSV file

Stored procedure SQL Server select insert

In the section, we will discuss how to create a stored procedure in SQL Server that uses the INSERT INTO SELECT statement. The INSERT INTO SELECT statement allows inserting data into table 1 from table 2 in a SQL Server database.

Now, we have already covered this topic in detail in the following tutorial on SQL Server. SQL Server stored procedure insert into select.

Insert stored procedure in SQL Server with parameters

As we all know a stored procedure in SQL Server is a collection of queries that are used to achieve some goal. Moreover, it is a prepared SQL code that is compiled and stored in a database and can be reused multiple times. 

Now, a stored procedure in SQL Server accepts two types of parameters. The first is an input parameter and, the second is the output parameter. For more details, refer to SQL Server stored procedure parameters.

Now, to insert some data into a table, we have to use the input parameters in a SQL Server stored procedure. The main goal of using input parameters is to pass some values from the user into a procedure. Further, those values will be used within the procedure to insert into the specified table.

Insert stored procedure in SQL Server example

For better understanding, let’s discuss an example in SQL Server. Again for this, we will use the Customertbl.

USE [sqlserverguides]
GO

CREATE PROCEDURE usp_InsertCustData
(
    @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @Email VARCHAR(50),
    @City VARCHAR(50),
    @Country VARCHAR(50)
)
AS
BEGIN
        INSERT INTO [Customertbl] VALUES (@first_name, @last_name, @Email, @City, @Country)
	    PRINT 'New Customer Data Inserted'
END

In the above procedure, we are creating a stored procedure that accepts 5 input parameters. Further, within the procedure, we are using the INSERT INTO TABLE statement to insert the parameter values in the Customertbl.

Moreover, when we successfully execute the stored procedure with the input parameters, the stored procedure will return a print message.

USE [sqlserverguides]
GO

EXEC usp_InsertCustData 'Sherie', 'Lumbers', 'slumbers1@harvard.edu', 
                        'Chicago', 'United States'

Here is the success message for the above query.

Moreover, we can also confirm the successful insertion by querying the Customertbl.

Read: How to test stored procedure in SQL Server

Stored procedure SQL Server update table

In this section, we will discuss how to create a stored procedure in SQL Server for updating the data of a table.

Again, for creating such stored procedures in SQL Server, we will use the input parameters of a stored procedure. The input parameters of a stored procedure will be used to pass updated values to a procedure. And the stored procedure will use the UPDATE statement to update the data.

Update stored procedure in SQL Server example

For better understanding, let’s discuss an example related to this stored procedure. And this time, we will create a new table with some data.

USE [sqlserverguides]
GO

CREATE TABLE Profile (
	user_id INT IDENTITY (101,1) NOT NULL,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	country VARCHAR(50),
	is_active VARCHAR(50)
);
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Kile', 'Hartopp', 'United States', 'False');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Aurore', 'Kobierra', 'Canada', 'True');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Calida', 'Brumbye', 'United States', 'False');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Masha', 'Annwyl', 'Canada', 'True');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Randie', 'Boddie', 'United States', 'False');

In the above query, we created a new Profile table in the sqlserverguides database. Moreover, we also inserted 5 records in the Profile table. The data of the Profile table is shown below.

Next, let’s, create a stored procedure in SQL Server that will update the value of the is_active column in the Profile table.

USE [sqlserverguides]
GO

CREATE PROCEDURE usp_UpdateProfile
( @userId INT )
AS
BEGIN
  UPDATE Profile 
  SET is_active='True' 
  WHERE user_id = @userId
END

The above procedure will take user-id as input and then update the is_active value to True for that user. Now, let’s execute the procedure by providing a user id.

USE [sqlserverguides]
GO

EXEC usp_UpdateProfile @userId = 105

Now let’s query the Profile table to check if the data is updated or not.

Read: Alter Stored Procedure in SQL Server

Stored procedure for bulk update in SQL Server

In this section, we will learn how to create a stored procedure in SQL Server for the bulk update operation.

So, in SQL Server, we have a BULK INSERT statement that is used to insert data from different files like CSV, Excel into a table. But, unfortunately, there is no BULK UPDATE statement in SQL Server.

Still, in this section, we will illustrate a different method to perform a bulk update operation using a stored procedure. And for this implementation, we will perform the following steps.

#1 Creating a new table

First, we will create a new table in SQL Server and also insert some records in it. Here is the query for this task.

USE [sqlserverguides]

CREATE TABLE Profile (
	user_id INT IDENTITY (101,1) NOT NULL,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	country VARCHAR(50),
	is_active VARCHAR(50)
);

INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Kile', 'Hartopp', 'United States', 'False');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Aurore', 'Kobierra', 'Canada', 'True');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Calida', 'Brumbye', 'United States', 'False');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Masha', 'Annwyl', 'Canada', 'True');
INSERT INTO Profile (first_name, last_name, country, is_active) VALUES ('Randie', 'Boddie', 'United States', 'False');

In the above query, we created a new Profile table in the sqlserverguides database. Moreover, we also inserted 5 records in the Profile table.

#2 Creating a table type

Next, we will create a user-defined table type in SQL Server. Now, a user-defined table type is a custom data type in SQL Server that holds data in tabular format. We will use this data type to pass data to the stored procedure.

USE [sqlserverguides]

CREATE TYPE  tblTypeProfile AS TABLE (
	user_id INT,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	country VARCHAR(50),
	is_active VARCHAR(50)
);

In the above query, we created a custom table in the sqlserverguides database. Moreover, the columns of this type are similar to the columns of the Profile table.

#3 Creating the stored procedure

Now that we have created a custom table type to pass tabular form of data to a stored procedure. It’s time to create a stored procedure for bulk insert in SQL Server.

USE [sqlserverguides]
GO

CREATE PROCEDURE uspBulkUpdateProfile  
( @ProfileTableType tblTypeProfile READONLY )  
AS  
BEGIN  
    MERGE Profile  AS dbProfile  
    USING @ProfileTableType AS tblTypePfl  
    ON (dbProfile.user_id  = tblTypePfl.user_id )  
  
    WHEN  MATCHED THEN  
        UPDATE SET  first_name = tblTypePfl.first_name,   
                    last_name = tblTypePfl.last_name,  
                    country = tblTypePfl.country,  
                    is_active = tblTypePfl.is_active
  
    WHEN NOT MATCHED THEN  
        INSERT (first_name, last_name, country, is_active)  
        VALUES (tblTypePfl.first_name, tblTypePfl.last_name, 
		tblTypePfl.country , tblTypePfl.is_active);  
END  

In the above query, we have created the procedure named uspBulkUpdateProfile. And it is accepting the table type created in the previous step as a parameter, also known as the table-valued parameter.

In the procedure, we have used the MERGE feature of the SQL Server to perform updates and insert in the same query.

#4 Execute the procedure

Now that the stored procedure is created, we can execute the above procedure to perform some updation and insertion.

For this task, first, we will declare the table type and insert some updated values in it. In the end, simply execute the procedure by providing the table type variable.

USE [sqlserverguides]
GO

-- Declaring the variable of table type  
DECLARE @tblTypeProfile tblTypeProfile  
  
--Inserting some records  
INSERT INTO @tblTypeProfile ([user_id], [first_name], [last_name], [country], [is_active])   
VALUES (101, 'Kile', 'Hartopp', 'United States', 'True'),
       (105, 'Randie', 'Boddie', 'United States', 'True');
  
  
--Executing procedure  
EXEC uspBulkUpdateProfile  @tblTypeProfile  

In the above example, first, we have declared a variable of user define table type. After this, we have inserted 2 updated records in the table variable. And in the end, we have executed the stored procedure and provided the table variable as input.

Now, if we query the table, we can see that those 2 records are updated in the Profile table.

Read: Rename stored procedure in SQL Server

Stored procedure in SQL Server for insert update delete

In this section, we will learn how to create a stored procedure in SQL Server for an insert, update, and delete operation at once.

Let’s, understand this implementation using an example in SQL Server. And for this, we will use the Customertbl table from the first section. And the SQL script to create a stored procedure is as follows.

USE [sqlserverguides]
GO

CREATE PROCEDURE usp_ManageCustomers 
( @cust_id INT,
  @first_name VARCHAR(50),
  @last_name VARCHAR(50),
  @Email VARCHAR(50),
  @City VARCHAR(50),
  @Country VARCHAR(50),
  @StmtType NVARCHAR(20) = ''  )
AS
  BEGIN
      IF @StmtType = 'INSERT'
        BEGIN
          INSERT INTO [Customertbl] 
	  VALUES (@first_name, @last_name, @Email, @City, @Country)
	  PRINT 'New Customer Data Inserted'
        END

      IF @StmtType = 'UPDATE'
        BEGIN
          UPDATE [Customertbl] SET 
	      first_name = @first_name,
	      last_name = @last_name,
	      city = @City,
              country = @Country,
	      Email = @Email
	      WHERE cust_id = @cust_id
	      PRINT 'Customer Data Updated'
        END

      ELSE IF @StmtType = 'DELETE'
        BEGIN
          DELETE FROM Customertbl
          WHERE  cust_id = @cust_id
          PRINT 'Customer Data Deleted'
        END
  END

In the above script, we have created a stored procedure that will accept 7 input values. However, out of those 7 parameters, 6 parameters are used for the table and 1 parameter will be used to determine the operation (INSERT, DELETE, or UPDATE).

Now, let’s execute the above stored procedure and understand how to perform all the operations.

USE [sqlserverguides]
GO
--Insert Operation
EXEC usp_ManageCustomers 6, 'Catharina', 'Janks', 'cjanks0@umn.edu', 
                         'Chicago', 'United States', 'INSERT';

--Update Operation
EXEC usp_ManageCustomers 6, 'Catharina', 'Janks', 'cjanks0@umn.edu', 
                         'Montreal', 'Canada', 'UPDATE';

--Delete Operation
EXEC usp_ManageCustomers 6, 'Catharina', 'Janks', 'cjanks0@umn.edu', 
                         'Montreal', 'Canada', 'DELETE';

In the above query, first, we are using the stored procedure to insert one record in the Customertbl. After this, we are using the stored procedure to update the same record. In the end, we are using the stored procedure to delete that record.

Here is the result of the above SQL code.

You may also like to read the following tutorials on SQL Server.

• SQL Server First Day Of Month
• Could not find stored procedure in SQL Server
• Loop in SQL Server stored procedure
• SQL Server find text in stored procedure
• How to execute stored procedure in SQL Server

So, in this tutorial, we have studied stored procedures in SQL Server for insert and update operations. Additionally, we have also discussed the following set of topics in this tutorial.

• Stored procedure for insert and update in SQL Server
• Stored procedure in SQL Server for inserting data into a table
• Stored procedure in SQL Server to insert into table
• Stored procedure SQL Server insert date
• Insert stored procedure in SQL Server with parameters
• Insert stored procedure in SQL Server example
• Stored procedure SQL Server bulk insert
• Stored procedure SQL Server select insert
• Stored procedure in SQL Server for insert update delete
• Stored procedure SQL Server update table
• Update stored procedure in SQL Server example
• Stored procedure for bulk update in SQL Server