Dummy database tables in SQL Server

sql server

← PrevNext →

Tools you can use

Last updated: 10th August 2022

Dummy database tables can be very useful in many ways, such as testing an application for data manipulation or data integrity. In addition, you can test other data driven tools such as a GridView control, exporting and importing data from various sources such as an Excel file.

A dummy database table with dummy data can help design other database objects such as tables, procedures, functions and triggers. It is very useful for DBAs, Database developers and application developers, to test various enterprise level operations.

👉 SQL Tip: How to get the first and last day of a given Month in SQL Server
get the first and last day of a given month

The Employee Table

To begin with, first create a dummy employee table and add few rows to it.

CREATE TABLE Employee
    (EmpID INT NOT NULL , 
        EmpName VARCHAR(50) NOT NULL, 
        Designation VARCHAR(50) NULL, 
        Department VARCHAR(50) NULL, 
        JoiningDate DATETIME NULL,
        CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED (EmpID)
    )
    
-- ADD ROWS TO THE TABLE.
    -- SQL SERVER 2008 AND ABOVE.

INSERT INTO Employee
    (EmpID, EmpName, Designation, Department, JoiningDate)
VALUES 
    (1, 'CHIN YEN', 'LAB ASSISTANT', 'LAB', GETDATE()),
    (2, 'MIKE PEARL', 'SENIOR ACCOUNTANT', 'ACCOUNTS', GETDATE()),
    (3, 'GREEN FIELD', 'ACCOUNTANT', 'ACCOUNTS', GETDATE()),
    (4, 'DEWANE PAUL', 'PROGRAMMER', 'IT', GETDATE()),
    (5, 'MATTS', 'SR. PROGRAMMER', 'IT', GETDATE()),
    (6, 'PLANK OTO', 'ACCOUNTANT', 'ACCOUNTS', GETDATE())


-- SQL SERVER 2005 AND BEFORE.

INSERT INTO Employee (EmpID, EmpName, Designation, Department, JoiningDate)
    SELECT 1, 'CHIN YEN', 'LAB ASSISTANT', 'LAB', GETDATE()
INSERT INTO Employee (EmpID, EmpName, Designation, Department, JoiningDate)
    SELECT 2, 'MIKE PEARL', 'SENIOR ACCOUNTANT', 'ACCOUNTS', GETDATE()
INSERT INTO Employee (EmpID, EmpName, Designation, Department, JoiningDate)
    SELECT 3, 'GREEN FIELD', 'ACCOUNTANT', 'ACCOUNTS', GETDATE()
INSERT INTO Employee (EmpID, EmpName, Designation, Department, JoiningDate)
    SELECT 4, 'DEWANE PAUL', 'PROGRAMMER', 'IT', GETDATE()
INSERT INTO Employee (EmpID, EmpName, Designation, Department, JoiningDate)
    SELECT 5, 'MATTS', 'SR. PROGRAMMER', 'IT', GETDATE()	
INSERT INTO Employee (EmpID, EmpName, Designation, Department, JoiningDate)
    SELECT 6, 'PLANK OTO', 'ACCOUNTANT', 'ACCOUNTS', GETDATE()
The Employee Detail Table

This table will hold miscellaneous details of Employees like the address, area, city, country and mobile number etc.

Employee detail table has a reference to the Employee table. (see above example)

CREATE TABLE [dbo].[EmployeeDetails](
	[EmpID] [int] NULL,
	[EmpName] [varchar](50) NULL,
	[Mobile] [varchar](10) NULL,
	[PresentAddress] [varchar](100) NULL,
	[Area] [varchar](50) NULL,
	[City] [varchar](50) NULL,
	[Country] [varchar](50) NULL,
	[Qualification] [varchar](50) NULL,
	[Email] [varchar](50) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[EmployeeDetails]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDetails_Employee]
    FOREIGN KEY([EmpID])
REFERENCES [dbo].[Employee] ([EmpID])
GO

ALTER TABLE [dbo].[EmployeeDetails] CHECK CONSTRAINT [FK_EmployeeDetails_Employee]
GO
    
-- ADD ROWS TO THE TABLE.
-- SQL SERVER 2008 AND ABOVE.
	    
INSERT INTO EmployeeDetails 
    (EmpID, EmpName, Mobile, PresentAddress, Area, City, Country, Qualification, Email)
VALUES
    (1, 'CHIN YEN', '1234567879', '1 MAIN AVE', 'WA', 'TACOCA', 'USA', 'GRADUATE','chinxyz@emp.com'),
    (2, 'MIKE PEARL', '2152313213', 'B BLOCK NICE STREET', 'WENEN', 'TACOCA', 'SCODD', 'GRADUATE', 'mike230@emample.com'),
    (3, 'GREEN FIELD', '4517825469', 'UNIVERSAL NEW AVE', 'BOURNNILE', 'BRISDON', 'NEW START', 'MANAGEMENT','greenden3939@emample.com'),
    (4, 'DEWANE PAUL', '5741115523', 'SPACE, 1 OF 1', 'ANDALUSIAN', 'PARALLEL', 'NEW RIVER', 'MATHEMATICS','dpaul_lime44@emample.com'),
    (5, 'MATTS', '4755#55522', 'NOT KNOWN', 'WOODOO', 'COMMONS', 'UNITED KINGDOM', 'ADVANCE PHYSICS','matts-ino33@emample.com'),
    (6, 'PLANK OTO', '5124785452', 'TUCSON, AZ MSA', 'ALPINE', 'ARIZONA', 'USA', 'DIPLOMA IN FINANCE','plaoto_nk984@emample.com')

-- SQL SERVER 2005 AND BEFORE.

INSERT INTO EmployeeDetails (EmpID, EmpName, Mobile, PresentAddress, Area, City,Country, Qualification, Email)
    SELECT 1, 'CHIN YEN', '1234567879', '1 MAIN AVE', 'WA', 'TACOCA', 'USA', 'GRADUATE','chinxyz@emp.com'
INSERT INTO EmployeeDetails (EmpID, EmpName, Mobile, PresentAddress, Area, City,Country, Qualification, Email)
    SELECT 2, 'MIKE PEARL', '2152313213', 'B BLOCK NICE STREET', 'WENEN', 'TACOCA', 'SCODD', 'GRADUATE', 'mike230@emample.com'
INSERT INTO EmployeeDetails (EmpID, EmpName, Mobile, PresentAddress, Area, City,Country, Qualification, Email)
    SELECT 3, 'GREEN FIELD', '4517825469', 'UNIVERSAL NEW AVE', 'BOURNNILE', 'BRISDON', 'NEW START', 'MANAGEMENT','greenden3939@emample.com'
INSERT INTO EmployeeDetails (EmpID, EmpName, Mobile, PresentAddress, Area, City,Country, Qualification, Email)
    SELECT 4, 'DEWANE PAUL', '5741115523', 'SPACE, 1 OF 1', 'ANDALUSIAN', 'PARALLEL', 'NEW RIVER', 'MATHEMATICS','dpaul_lime44@emample.com'
INSERT INTO EmployeeDetails (EmpID, EmpName, Mobile, PresentAddress, Area, City,Country, Qualification, Email)
    SELECT 5, 'MATTS', '4755#55522', 'NOT KNOWN', 'WOODOO', 'COMMONS', 'UNITED KINGDOM', 'ADVANCE PHYSICS','matts-ino33@emample.com'
INSERT INTO EmployeeDetails (EmpID, EmpName, Mobile, PresentAddress, Area, City,Country, Qualification, Email)
    SELECT 6, 'PLANK OTO', '5124785452', 'TUCSON, AZ MSA', 'ALPINE', 'ARIZONA', 'USA', 'DIPLOMA IN FINANCE','plaoto_nk984@emample.com'
The "Books" table

Now, here's another table that would be useful for testing small aplications, especially Single Page Applications using AngularJS etc.

CREATE TABLE [dbo].[Books](
    [BookID] [int] IDENTITY(1,1) NOT NULL,
    [BookName] [varchar](50) NULL,
    [Category] [varchar](50) NULL,
    [Price] [numeric](18, 2) NULL,
    [Price_Range] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]

GO

-- ADD FEW ROWS TO THE TABLE.
-- SQL SERVER 2008 AND ABOVE.

INSERT INTO dbo.Books
    (BookName, Category, Price, Price_Range)
VALUES	
    ('Computer Architecture', 'Computers', 125.6, '100-150'),
    ('Advanced Composite Materials', 'Science', 172.56, '150-200'),
    ('Asp.Net 4 Blue Book', 'Programming', 56.00, '50-100'),
    ('Strategies Unplugged', 'Science', 99.99, '50-100'),
    ('Teaching Science', 'Science', 164.10, '150-200'),
    ('Challenging Times', 'Business', 150.70, '150-200'),
    ('Circuit Bending', 'Science', 112.00, '100-150'),
    ('Popular Science', 'Science', 210.40, '200-250'),
    ('ADOBE Premiere', 'Computers', 62.20, '50-100')
	
-- SQL SERVER 2005 AND BEFORE.	

INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Computer Architecture', 'Computers', 125.6, '100-150'
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Advanced Composite Materials', 'Science', 172.56, '150-200'	
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Asp.Net 4 Blue Book', 'Programming', 56.00, '50-100'	
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Strategies Unplugged', 'Science', 99.99, '50-100'
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Teaching Science', 'Science', 164.10, '150-200'
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Challenging Times', 'Business', 150.70, '150-200'
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Circuit Bending', 'Science', 112.00, '100-150'
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'Popular Science', 'Science', 210.40, '200-250'
INSERT INTO dbo.Books (BookName, Category, Price, Price_Range)
    SELECT 'ADOBE Premiere', 'Computers', 62.20, '50-100'

The Birds table

The dbo.Birds table has a list of birds with the bird names, various types and their scientific names. It is again useful for testing small apps.

CREATE TABLE [dbo].[Birds](
    [ID] [int] NOT NULL,
    [BirdName] [varchar](50) NULL,
    [TypeOfBird] [varchar](50) NULL,
    [ScientificName] [varchar](50) NULL,
    CONSTRAINT [PK_Birds] PRIMARY KEY CLUSTERED 
    ([ID] ASC)
) ON [PRIMARY]
        
GO

-- ADD FEW ROWS TO THE TABLE.
-- SQL SERVER 2008 AND ABOVE.

INSERT INTO dbo.Birds (ID, BirdName, TypeOfBird, ScientificName)
VALUES 
    (1,	'Eurasian Collared-Dove', 'Dove', 'Streptopelia'),
    (2,	'Bald Eagle	Hawk', 'Haliaeetus', 'Leucocephalus'),
    (3,	'Coopers Hawk',	'Hawk',	'Accipiter Cooperii'),
    (4,	'Bells Sparrow', 'Sparrow', 'Artemisiospiza Belli'),
    (5,	'Mourning Dove', 'Dove', 'Zenaida Macroura'),
    (6,	'Rock Pigeon', 'Dove', 'Columba Livia'),
    (7,	'Aberts Towhee', 'Sparrow', 'Melozone Aberti'),
    (8,	'Brewers Sparrow', 'Sparrow', 'Spizella Breweri'),
    (9,	'Canyon Towhee', 'Sparrow', 'Melozone Fusca'),
    (10, 'Black Vulture', 'Hawk', 'Coragyps Atratus'),
    (11, 'Gila Woodpecker', 'Woodpecker', 'Melanerpes Uropygialis'),
    (12, 'Gilded Flicker', 'Woodpecker', 'Colaptes Chrysoides'),
    (13, 'Cassins Sparrow', 'Sparrow', 'Peucaea Cassinii'),
    (14, 'American Kestrel', 'Hawk', 'Falco Sparverius'),
    (15, 'Hairy Woodpecker', 'Woodpecker', 'Picoides villosus'),
    (16, 'Lewis Woodpecker', 'Woodpecker', 'Melanerpes Lewis'),
    (17, 'Snail Kite', 'Hawk', 'Rostrhamus Sociabilis'),
    (18, 'White-tailed Hawk', 'Hawk', 'Geranoaetus Albicaudatus')

1) How to find and remove Duplicate rows in a Table using SQL Server ROW_NUMBER() and CTE: Duplicate rows in tables can be very annoying for DBA’s and programmers, as it raises many uncomfortable questions about the authenticity of data in a database. The matter gets worse when company auditors complain about irregularities in the balance sheet etc.

2) How to convert Rows into Columns using SQL Server PIVOT OR how to use PIVOT in SQL Server: Ever wondered how you can convert data from rows to columns in SQL Server. We are talking about an SQL query, which will transform records from multiple rows into columns. Using SQL Server PIVOT, we can efficiently rotate a table’s data to show a summarized result.

3) Insert Multiple rows with a Single INSERT Statement using SQL Server Table Value Constructor: While managing an Inventory management System for an organization, I have came across a situation where I had to perform bulk upload on a table in SQL Server. Bulk upload requires inserting multiple rows of data in a table.

4) How to Convert Month Number in a Date to Month Name in SQL Server: Let us assume I have a Sales table with sales data for each month. Every day sales is stored in the table with columns such as date, quantity, price etc. I want to get the total sales for every month. Now since I have a column with “date” data type, I want to convert the month number in the date to Month Name (like February, March etc.). Find out how this is done in SQL Server.

5) SQL Server CHARINDEX Function with Examples: The primary use of an SQL Server CHARINDEX function is to find the first or starting location of an expression or characters in a given string. To make it simple, it is like searching a specified character or characters in a string.

Thanks for reading. ☺

← PreviousNext →