John J. Owens

If you need to insert multiple records at the same time. Writing out those SQL insert queries can be laborious. Setting the same fields and then amending the values.

USE [ExampleDatabase];

INSERT INTO Employee (FirstName, LastName) VALUES ('Joe', 'Bloggs');
INSERT INTO Employee (FirstName, LastName) VALUES ('Jane', 'Bloggs');
INSERT INTO Employee (FirstName, LastName) VALUES ('Peter', 'Bloggs');
INSERT INTO Employee (FirstName, LastName) VALUES ('Mary', 'Bloggs');
INSERT INTO Employee (FirstName, LastName) VALUES ('Paul', 'Bloggs');
INSERT INTO Employee (FirstName, LastName) VALUES ('Lisa', 'Bloggs');
Code language: SQL (Structured Query Language) (sql)

You can batch insert several records with one INSERT command and add new values on each line as many you like.

USE [ExampleDatabase];

INSERT INTO Employee
(FirstName, LastName)
VALUES
('Joe', 'Bloggs'),
('Jane', 'Bloggs'),
('Peter', 'Bloggs'),
('Mary', 'Bloggs'),
('Paul', 'Bloggs'),
('Lisa', 'Bloggs');
Code language: SQL (Structured Query Language) (sql)

We’re going to cover on how to write a stored procedure with a series of stored procedures. That means running a number of stored procedures within one stored procedure.

You might do this for effiency and to avoid duplicating your SELECT/INSERT queries. We will create Stored Procedures to return primary keys to be subsequently used in SELECT or INSERT queries.

We’ll be using SQL Server 2017. The version of SQL Server version does not matter as long as it’s a recent version.

1. Create or use a test database of your choice. I’m using a test database called Example Database
2. Run the SQL query below to create new tables; Country, Town and Branch

USE [ExampleDatabase]
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE [dbo].[Country](
     [CountryID] [int] IDENTITY(1,1) NOT NULL,
     [CountryName] <a href="120">varchar</a> NOT NULL,
  CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
 (
     [CountryID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 CREATE TABLE [dbo].[Town](
     [TownID] [int] IDENTITY(1,1) NOT NULL,
     [TownName] <a href="120">varchar</a> NOT NULL,
     [CountryID] [int] NOT NULL,
  CONSTRAINT [PK_Town] PRIMARY KEY CLUSTERED 
 (
     [TownID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
 CREATE TABLE [dbo].[Branch](
     [BranchID] [int] IDENTITY(1,1) NOT NULL,
     [BranchName] <a href="120">varchar</a> NOT NULL,
     [TownID] [int] NOT NULL,
     [CountryID] [int] NOT NULL,
  CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED 
 (
     [BranchID] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
 GO
Code language: SQL (Structured Query Language) (sql)

3. We’ll create 3 new Stored Procedures. Look up “Programmability” node in your database and right click “Stored Procedure…”. This will create a new Query window.

We’ll add a new Stored Procedure to add a new Country. Remember to change name of the database [ExampleDatabase] to your name of database.

USE [ExampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddCountry]
	@CountryName varchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @CountryID as int = 0;

	IF EXISTS(SELECT * FROM [dbo].[Country] WHERE CountryName LIKE @CountryName)
	BEGIN 
		SELECT @CountryID=CountryID FROM [dbo].[Country] WHERE CountryName LIKE @CountryName
	END
	ELSE
	BEGIN
		INSERT INTO [dbo].[Country] (CountryName) VALUES (@CountryName);
		SELECT @CountryID = @@IDENTITY 
	END

	RETURN @CountryID
END

Code language: SQL (Structured Query Language) (sql)

The above Stored Procedure accepts one parameter, CountryName. It checks if the CountryName alreadys exists in the table. If it doesn’t, it creates a new Country and gets the latest primary key and stores this in the variable ID. Otherwise, it will get the Primary Key from the existing record

USE [ExampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddTown]
	@TownName varchar(MAX), 
	@CountryID as int
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @TownID as int = 0;

    IF NOT EXISTS(SELECT * FROM [dbo].[Town] WHERE TownName LIKE @TownName AND CountryID=@CountryID)
	BEGIN 
		INSERT INTO [dbo].[Town] ([TownName],[CountryID]) VALUES (@TownName, @CountryID);
		SELECT @TownID = @@IDENTITY 
	END
	ELSE
	BEGIN
		SELECT @TownID=TownID FROM [dbo].[Town] WHERE TownName LIKE @TownName AND CountryID=@CountryID
	END

	RETURN @TownID
END

Code language: SQL (Structured Query Language) (sql)

The Stored Procedure accepts two parameters, TownName and CountryID. It will check if the record already exists by querying for the TownName and CountryID. You can get same town names but in different countries. We want to avoid creating duplicate records. If a record doesn’t exist, it will create a new record with TownName and the CountryID. By adding the CountryID to the Town record, we are creating a Foreign Key where we can link Towns to Countries.

If a Town record exist, it will get the primary key, TownID, from the table Town.

One last Stored Procedure to add. In this one, we’ll create a new Branch record. This record can be a branch part of your chain of shops like coffee, clothes, or fast food.

USE [ExampleDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddBranch]
	 @BranchName as varchar(MAX),
	 @TownName as varchar(MAX),
	 @CountryName as varchar(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @BranchID as int = 0;
	DECLARE @CountryID as int = 0;
	DECLARE @TownID as int = 0;

	-- GET COUNTRY ID
	EXEC @CountryID = AddCountry @CountryName;

	-- GET TOWN ID
	EXEC @TownID = AddTown @TownName, @CountryID;

	-- GET BRANCH ID
	IF EXISTS(SELECT * FROM [dbo].[Branch] WHERE BranchName LIKE @BranchName AND CountryID=@CountryID AND TownID=@TownID)
	BEGIN 
		SELECT @BranchID=BranchID FROM [dbo].[Branch] WHERE BranchName LIKE @BranchName AND CountryID=@CountryID AND TownID=@TownID
	END
	ELSE
	BEGIN
		INSERT INTO [dbo].[Branch] (BranchName, TownID, CountryID) VALUES (@BranchName, @TownID, @CountryID);
		SELECT @BranchID = @@IDENTITY 
	END

END

Code language: SQL (Structured Query Language) (sql)

The above Stored Procedures has two Stored Procedures ready to be executed. Those Stored procedures will return CountryID and TownID which we can subsequently use to query for existing Branch records. If none exists, we can create a new Branch record with the branch name, Town ID and Country ID. It has logic checks added to prevent duplicate records from being built.

4. Time to test the Stored Procedure

USE [ExampleDatabase];

-- DELETE ALL RECORDS
TRUNCATE TABLE Town;
TRUNCATE TABLE Country;
TRUNCATE TABLE Branch;

exec AddBranch 'Branch Alpha', 'Glasgow', 'Scotland'
exec AddBranch 'Branch Beta', 'Edinburgh', 'Scotland'
exec AddBranch 'Branch Charlie', 'Manchester', 'England'
exec AddBranch 'Branch Delta', 'Liverpool', 'England'
exec AddBranch 'Branch Echo', 'Cardiff', 'Wales'
exec AddBranch 'Branch Fox', 'Swansea', 'Wales'

-- GET ALL BRANCHES
SELECT  
b.BranchID,
b.BranchName,
t.TownID, 
t.TownName, 
c.CountryID, 
CountryName FROM Branch as b
INNER JOIN Town as t ON t.TownID=b.TownID
INNER JOIN Country as c ON c.CountryID=b.CountryID
Code language: SQL (Structured Query Language) (sql)

After executing the Stored Procedure to add new Branches, it will then run a query to get all branches. It will list the Branch name, Town and Country details.

In the Stored Procedure AddBranch, it will create or find Town and Country records. Then those records IDs are used to create a new Branch if it doesn’t already exists.