Microsoft SQL Server

INSERT / SELECT / UPDATE / DELETE: basics SQL Statements

INSERT / SELECT / UPDATE / DELETE: the basics SQL Statements

Data Manipulation Language (DML for short) includes operations such as INSERT, UPDATE and DELETE:

sql">-- Create a table HelloWorld



Description VARCHAR(1000)


DML Operation INSERT, inserting a row into the table INSERT INTO HelloWorld (Description) VALUES ('Hello World')
DML Operation SELECT, displaying the table

SELECT * FROM HelloWorld

Select a specific column from table SELECT Description FROM HelloWorld
Display number of records in the table SELECT Count(*) FROM HelloWorld
DML Operation UPDATE, updating a specific row in the table

UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1

Selecting rows from the table (see how the Description has changed after the update?) SELECT * FROM HelloWorld
DML Operation - DELETE, deleting a row from the table


-- Selecting the table. See table content after DELETE operation


SELECT * FROM HelloWorld
SELECT, UPDATE, DELETE & Comments code in SQL

In this script we’re creating a table to demonstrate some basic queries.

The following examples are showing how to query tables:

sql">USE Northwind;


SELECT TOP 10 * FROM Customers

ORDER BY CompanyName

will select the first 10 records of the Customer table, ordered by the column CompanyName from the database

Northwind (which is one of Microsoft’s sample databases, it can be downloaded from here):

Note that Use Northwind; changes the default database for all subsequent queries. You can still reference the database by using the fully qualified syntax in the form of [Database].[Schema].[Table]:

AWS Lambda triggered by S3
sql">SELECT TOP 10 * FROM Northwind.dbo.Customers

ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors


This is useful if you’re querying data from different databases. Note that dbo, specified “in between” is called a schema and needs to be specified while using the fully qualified syntax. You can think of it as a folder within your database. dbo is the default schema. The default schema may be omitted. All other user defined schemas need to be specified.

If the database table contains columns which are named like reserved words, e.g. Date, you need to enclose the column name in brackets, like this:

How to backup archivelog for specific sequence RMAN
sql">-- descending order

SELECT TOP 10 [Date] FROM dbo.MyLogTable


The same applies if the column name contains spaces in its name (which is not recommended). An alternative syntax is to use double quotes instead of square brackets, e.g.:

sql">-- descending order

SELECT top 10 "Date" from dbo.MyLogTable

order by "Date" desc

is equivalent but not so commonly used. Notice the difference between double quotes and single quotes: Single quotes are used for strings, i.e.

sql">-- descending order

SELECT top 10 "Date" from dbo.MyLogTable

where UserId='johndoe'

order by "Date" desc

is a valid syntax. Notice that T-SQL has a N prefix for NChar and NVarchar data types, e.g.

sql">SELECT TOP 10 * FROM Northwind.dbo.Customers

WHERE CompanyName LIKE N'AL%'

ORDER BY CompanyName

returns all companies having a company name starting with AL (% is a wild card, use it as you would use the asterisk in a DOS command line, e.g. DIR AL*). For LIKE, there are a couple of wildcards available, look here to find out more details.

Ansible Interview Questions and Answers 1


Joins are useful if you want to query fields which don’t exist in one single table, but in multiple tables. For example: You want to query all columns from the Region table in the Northwind database. But you notice that you require also the RegionDescription, which is stored in a different table, Region. However, there is a common key, RgionID which you can use to combine this information in a single query as follows (Top 5 just returns the first 5 rows, omit it to get all rows):

sql">SELECT TOP 5 Territories.*,


FROM Territories


ON Territories.RegionID=Region.RegionID

ORDER BY TerritoryDescription

will show all columns from Territories plus the RegionDescription column from Region. The result is ordered by TerritoryDescription.

Table Aliases

When your query requires a reference to two or more tables, you may find it useful to use a Table Alias. Table aliases are shorthand references to tables that can be used in place of a full table name, and can reduce typing and editing. The syntax for using an alias is:

<TableName> [as] <alias>

Backup QNAP Turbo NAS data using CloudBackup Station

Where as is an optional keyword. For example, the previous query can be rewritten as:

sql">SELECT TOP 5 t.*,


FROM Territories t


ON t.RegionID = r.RegionID

ORDER BY TerritoryDescription

Aliases must be unique for all tables in a query, even if you use the same table twice. For example, if your Employee table included a SupervisorId field, you can use this query to return an employee and his supervisor’s name:

sql">SELECT e.*,

s.Name as SupervisorName -- Rename the field for output FROM Employee e

INNER JOIN Employee s

ON e.SupervisorId = s.EmployeeId


WHERE e.EmployeeId = 111


As we have seen before, a Join adds columns from different table sources. But what if you want to combine rows from different sources? In this case you can use a UNION. Suppose you’re planning a party and want to invite not only employees but also the customers. Then you could run this query to do it:

sql">SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees


SELECT ContactName, Address, City FROM Customers

It will return names, addresses and cities from the employees and customers in one single table. Note that duplicate rows (if there should be any) are automatically eliminated (if you don’t want this, use a UNION ALL instead). The column number, column names, order and data type must match across all the select statements that are part of the union – this is why the first SELECT combines FirstName and LastName from Employee into ContactName.

How to check last modified table in Oracle

Table Variables

It can be useful, if you need to deal with temporary data (especially in a stored procedure), to use table variables:

The difference between a “real” table and a table variable is that it just exists in memory for temporary processing.


sql">DECLARE @Region TABLE


RegionID int,

RegionDescription NChar(50)


creates a table in memory. In this case the @ prefix is mandatory because it is a variable. You can perform all DML operations mentioned above to insert, delete and select rows, e.g.

INSERT INTO @Region values(3,'Northern')

INSERT INTO @Region values(4,'Southern')

But normally, you would populate it based on a real table like


SELECT * FROM dbo.Region WHERE RegionID>2;

which would read the filtered values from the real table dbo.Region and insert it into the memory table @Region – where it can be used for further processing. For example, you could use it in a join like

sql">SELECT * FROM Territories t

JOIN @Region r on t.RegionID=r.RegionID


which would in this case return all Northern and Southern territories. More detailed information can be found here.

CloudHook Migration

Temporary tables are discussed here, if you are interested to read more about that topic.

NOTE: Microsoft only recommends the use of table variables if the number of rows of data in the table variable are less than 100. If you will be working with larger amounts of data, use a temporary table, or temp table, instead.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.