How to Create Table & Insert Records in Microsoft Fabric Data Warehouse SQL Endpoint

Abiola David
Oct 16, 2023

4.8k
0
1
- facebook
- twitter
- linkedIn
- Reddit
- WhatsApp
- Email
- Print
- Other Artcile

Introduction

Welcome to the Excel Jet Consult blog post. Microsoft Fabric Data Warehouse is a cloud-based data platform that enables you to store, analyze, and share large volumes of structured and semi-structured data. SQL Endpoint is a service that allows you to run SQL queries on your data warehouse using a web-based interface.

How to Create Table and Insert Records?

One of the basic operations you can perform on your data warehouse is creating a table. A table is a collection of rows and columns that store related data. You can use the CREATE TABLE Transact-SQL statement to create a table in your data warehouse.

The syntax of the CREATE TABLE statement is as follows.

CREATE TABLE [ schema_name . ] table_name
( { column_definition | table_constraint } [ ,...n ] )

The parameters of the statement.

schema_name: The name of the schema to which the table belongs. If not specified, the default schema of the current user is used.
table_name: The name of the table to be created. The name must be unique within the schema.
column_definition: The definition of a column in the table, including its name, data type, and optional constraints.
table_constraint: A constraint that applies to the whole table, such as a primary key, a foreign key, or a check constraint.

We want to create a table called Employees_Records in the SQL end-point and insert a few records into the table.

The first thing we need to do is to create a workspace.

In this blog, we created the HR_Database workspace.
Next, at the bottom left corner of the screen, we switch to the Data Warehouse experience.

In the Synapse Data Warehouse, click on Warehouse (Preview).
Provide the name for the Warehouse. Employee details are provided in this article.

Click Create

As seen in the screenshot below, we have within the Employees Details warehouse.

Click on T-SQL to open the SQL endpoint where we can write, create, and insert SQL commands.
Click on New SQL query from the ribbon.
In the SQL query1 window, execute the Create statement and Insert command.

--Create table 
CREATE TABLE Employees_Records (
    EmpID INT,
    Full_Name VARCHAR(255) NOT NULL,
    Department VARCHAR(255),
    Salary DECIMAL(10, 2)
);

--Insert into the table
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (1, 'John Smith', 'HR', 55000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (2, 'Alice Johnson', 'IT', 65000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (3, 'Michael Brown', 'Finance', 60000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (4, 'Sarah Davis', 'Marketing', 58000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (5, 'David Wilson', 'IT', 70000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (6, 'Jessica Lee', 'Sales', 54000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (7, 'Brian Hall', 'Engineering', 72000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (8, 'Linda Martinez', 'Finance', 62000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (9, 'Robert Clark', 'HR', 56000.00);
INSERT INTO Employees_Records (EmpID, Full_Name, Department, Salary)
VALUES (10, 'Jennifer Adams', 'Marketing', 59000.00);