Practical No.
9
Create a Data Staging Area
for a Selected Database Using SQL
SQL Server Management Studio (SSMS) — Step-by-Step Visual Guide
Objective: Create a staging database (StagingDB), build staging tables to temporarily hold raw/incoming data, insert
sample records, and verify the staging area using SQL queries — all inside SSMS.
What is a Data Staging Area?
A Data Staging Area (DSA) is an intermediate storage zone used in ETL (Extract–Transform–Load) pipelines. Raw
data from source systems lands here first, is cleaned / validated, then moved to the data warehouse. Staging tables
are typically prefixed with stg_ to distinguish them from final warehouse tables.
Tools Required: Microsoft SQL Server + SSMS | Estimated Time: 20–30 minutes
Launch SSMS & Connect to SQL Server
1 Open SQL Server Management Studio from Start Menu
1 Open SSMS: Start → Search 'SSMS' → Click Microsoft SQL Server Management Studio
Connect to Server
Database Engine
Server type:
Enter server name
Server name: localhost\SQLEXPRESS
Select Authentication
Authentication: Windows Authentication
Click Connect
Connect Cancel
What to do: In the Connect to Server dialog — set Server type to Database Engine, enter your Server name (e.g.
localhost\SQLEXPRESS or just .), keep Authentication as Windows Authentication (or enter SQL login), then click
Connect.
Tip: The orange dashed rectangles and arrows in the screenshots above show exactly which field / button to
interact with at each step.
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 1
Open a New Query Window
2 Use the toolbar button or Ctrl+N
2 Click the 'New Query' button in the toolbar —OR— press Ctrl + N
Microsoft SQL Server Management Studio
File Edit View Query Tools Window Help
Click New Query
New Query
Object Explorer
Right-click Databases
Query Editor / Results Area
localhost\SQLEXPRESS
Databases
+ master
+ tempdb
+ StagingDB
What to do: After connecting, the SSMS main window opens. Click the highlighted New Query button (top-left
toolbar). A blank SQL editor tab will appear on the right side. You can also see your server & databases in the Object
Explorer panel on the left.
Create the Staging Database (StagingDB)
3 Paste & run the CREATE DATABASE statement
3 In the query editor, type or paste the SQL below, then press F5 or click Execute
[Link] - StagingDB (sa)
Press F5 or Click Execute
Ensure StagingDB selected
! Execute StagingDB v
Paste SQL here
-- Step 1: Create Staging Database
CREATE DATABASE StagingDB;
GO
USE StagingDB;
GO
-- Step 2: Create Staging Table
CREATE TABLE stg_Employees (
EmployeeID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
Salary DECIMAL(10,2),
LoadDate DATETIME DEFAULT GETDATE()
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 2
);
SQL to Paste — Step 3a: Create Database
-- Create the Staging Database
CREATE DATABASE StagingDB;
GO
-- Switch to the new database
USE StagingDB;
GO
What to do: Paste the SQL above into the editor. Make sure the database dropdown (toolbar) shows master before
running. Press F5 or click the green ! Execute button. The Messages tab will show: Command(s) completed
successfully.
Note: After USE StagingDB; all subsequent statements run inside the new staging database. The dropdown
will switch to StagingDB automatically.
Create Staging Tables
4 Tables prefixed with stg_ to hold raw incoming data
4 Paste the CREATE TABLE statements below and press F5 to execute
SQL to Paste — Step 4: Create Staging Tables
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 3
USE StagingDB;
GO
-- Staging table for Employees
CREATE TABLE stg_Employees (
EmployeeID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10,2),
SourceSystem VARCHAR(50),
LoadDate DATETIME DEFAULT GETDATE(),
IsProcessed BIT DEFAULT 0
);
GO
-- Staging table for Orders
CREATE TABLE stg_Orders (
OrderID INT,
CustomerID INT,
OrderDate DATETIME,
TotalAmount DECIMAL(12,2),
Status VARCHAR(20),
SourceSystem VARCHAR(50),
LoadDate DATETIME DEFAULT GETDATE(),
IsProcessed BIT DEFAULT 0
);
GO
-- Staging table for Products
CREATE TABLE stg_Products (
ProductID INT,
ProductName VARCHAR(200),
Category VARCHAR(100),
Price DECIMAL(10,2),
StockQty INT,
SourceSystem VARCHAR(50),
LoadDate DATETIME DEFAULT GETDATE(),
IsProcessed BIT DEFAULT 0
);
GO
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 4
Column Explanation: SourceSystem — tracks which system sent the data; LoadDate — auto-stamp when record
was loaded; IsProcessed — flag set to 1 after the record is moved to the warehouse.
Verify Tables in Object Explorer
5 Refresh and expand StagingDB → Tables
5 In Object Explorer: Expand StagingDB → Tables (press F5 to refresh first)
Object Explorer — StagingDB
StagingDB
Database Diagrams
Tables
Staging tables created!
System Tables
dbo.stg_Employees
dbo.stg_Orders
dbo.stg_Products
Views
Stored Procedures
What to do: In the left Object Explorer panel, expand StagingDB → Tables. You should see all three staging tables
highlighted in green: dbo.stg_Employees, dbo.stg_Orders, and dbo.stg_Products. If the tables don't appear,
right-click Tables and select Refresh.
Insert Sample Data into Staging Tables
6 Simulate loading raw data from source systems
6 Paste the INSERT statements below and press F5 to load sample data
SQL to Paste — Step 6: Insert Sample Records
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 5
USE StagingDB;
GO
-- Insert into stg_Employees
INSERT INTO stg_Employees (EmployeeID,FirstName,LastName,Department,Salary,SourceSystem)
VALUES
(101, 'John', 'Doe', 'HR', 55000.00, 'ERP_System'),
(102, 'Jane', 'Smith', 'Finance', 62000.00, 'ERP_System'),
(103, 'Alice', 'Brown', 'IT', 75000.00, 'HRMS'),
(104, 'Bob', 'Wilson', 'Sales', 48000.00, 'HRMS');
GO
-- Insert into stg_Orders
INSERT INTO stg_Orders (OrderID,CustomerID,OrderDate,TotalAmount,Status,SourceSystem)
VALUES
(1001, 501, '2024-01-10', 1500.00, 'Pending', 'WebStore'),
(1002, 502, '2024-01-11', 2300.50, 'Completed', 'WebStore'),
(1003, 503, '2024-01-12', 875.00, 'Pending', 'MobileApp');
GO
-- Insert into stg_Products
INSERT INTO stg_Products (ProductID,ProductName,Category,Price,StockQty,SourceSystem)
VALUES
(201, 'Laptop Pro', 'Electronics', 85000.00, 50, 'InventoryDB'),
(202, 'Wireless Mouse','Accessories', 1200.00, 200, 'InventoryDB'),
(203, 'USB-C Hub', 'Accessories', 2500.00, 150, 'InventoryDB');
GO
Query the Staging Area to Verify Data
7 Run SELECT statements and view results
7 Paste the SELECT queries below and press F5 — check the Results tab
SQL to Paste — Step 7: Verify Data
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 6
USE StagingDB;
GO
-- View all staged employees
SELECT * FROM stg_Employees;
-- View pending orders
SELECT * FROM stg_Orders WHERE Status = 'Pending';
-- Count of records per table
SELECT 'stg_Employees' AS TableName, COUNT(*) AS RecordCount FROM stg_Employees
UNION ALL
SELECT 'stg_Orders', COUNT(*) FROM stg_Orders
UNION ALL
SELECT 'stg_Products', COUNT(*) FROM stg_Products;
Query executed successfully — Results
INSERT INTO stg_Employees VALUES(101,'John','Doe',55000,GETDATE());
INSERT INTO stg_Employees VALUES(102,'Jane','Smith',62000,GETDATE());
SELECT * FROM stg_Employees;
Data inserted & visible in staging
Results Messagesarea!
EmpID FirstName LastName Salary LoadDate
101 John Doe 55000.00 2024-01-15 10:30
102 Jane Smith 62000.00 2024-01-15 10:30
(2 row(s) affected) — Command(s) completed successfully.
What you should see: The Results tab shows the employee records you inserted. The Messages tab confirms (4
row(s) affected). The UNION ALL query shows record counts: Employees=4, Orders=3, Products=3.
Additional Staging Operations (Bonus)
8 Truncate, update flags, and cleanup commands
8 These are commonly used maintenance operations on a staging area
SQL to Paste — Step 8: Maintenance Queries
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 7
-- Mark records as processed after moving to warehouse
UPDATE stg_Employees SET IsProcessed = 1 WHERE IsProcessed = 0;
-- Clear the staging table for the next load (TRUNCATE is faster than DELETE)
TRUNCATE TABLE stg_Employees;
-- Add an index to improve load performance
CREATE INDEX IX_stg_Emp_EmpID ON stg_Employees(EmployeeID);
-- View table structure
EXEC sp_help 'stg_Employees';
-- Drop staging database (only if no longer needed)
-- DROP DATABASE StagingDB;
Key Concepts: TRUNCATE removes all rows but keeps the table structure — much faster than DELETE for staging
resets. IsProcessed = 1 flags records that have been moved to the warehouse. Never drop the staging DB during
normal operations — it's a flag in the SQL comment.
Summary — What You Have Accomplished
✓
You have successfully completed Practical No. 9. Here is a recap of every action performed:
Step Action SQL Command Used
1 Launched SSMS & connected to SQL Server —
2 Opened a New Query window Ctrl+N
3 Created the Staging Database CREATE DATABASE StagingDB
4 Created 3 Staging Tables CREATE TABLE stg_*
5 Verified tables in Object Explorer Refresh → Expand Tables
6 Inserted sample data INSERT INTO stg_*
7 Queried the staging area SELECT * FROM stg_*
8 Performed maintenance ops UPDATE / TRUNCATE / CREATE INDEX
Result: A fully operational Data Staging Area database (StagingDB) with three staging tables, sample data loaded,
and verified — ready to act as an ETL intermediate layer before data is transformed and loaded into a data
warehouse.
End of Practical No. 9 — Data Staging Area using SQL
Practical No. 9 — Data Staging Area using SQL | SSMS Step-by-Step Guide Page 8