MSSQL Statements Syntax

Select Statement
Update Statement
Insert Statement
Delete Statement
Select Into Statement

Many times I forget the exact syntax I should use whether I'm writing in MSSQL, My-SQL or Oracle.  This post is the first out of three posts designed to gather basic database statements and show a simple and clean example to them.

Select Statement:

Syntax 1:  SELECT column_1, column_3
                     FROM Table

Example: SELECT ID, Name
                FROM tbl_customers

Syntax 2: SELECT * FROM Table (meaning - select all columns).

Example: SELECT * FROM tbl_customers

Update Statement:

Syntax 1: UPDATE Table
                    column_1 = 'value'

Example: UPDATE tbl_customers
                    Name = 'Elad'

This is a very bad example for UPDATE statement since this one will update -ALL- the names to 'Elad'.
Syntax 2: UPDATE Table
                    column_1 = 'value'
                   WHERE column_2 = 'Dependency_Term'

Example: UPDATE tbl_customers
                   Name = 'Elad'
                   WHERE ID = 13 AND Name = 'Noam'

Insert Statement:

Syntax 1: INSERT INTO Table
                   VALUES ('value', 'value', 'value')

Example 1: INSERT INTO tbl_customers
                       VALUES (15, 'Elad', 28, 1)

This is a very bad example for INSERT statements since in this one you will have to write values for every column in you table.  Even if it's nullable.
Syntax 2: INSERT INTO Table (column_1, column_2, column_3)
                    VALUES ('value_1', 'value_2', 'value3')

Example 2: INSERT INTO tbl_customers (Name, Age, Gender)
                      VALUES ('Elad', 28, 1)

Delete Statement:

Syntax 1: DELETE FROM Table

Example 1: DELETE FROM tbl_customers

This is a very bad example for DELETE statement since this one will clean you table but will keep your rows number record.
The following syntax is better for deleting a specific row..:

Syntax 2: DELETE FROM Table
                    WHERE column_1 = 'value'

Example 2: DELETE FROM tbl_customers
                      WHERE ID = 13

Select Into Statement:

The SELECT INTO statement is designed to copy data from one table to another.
 Syntax 1: SELECT column_1, column_2
                    INTO Table_2
                   FROM Table_1

Example 1: SELECT ID, Name
                      INTO tbl_affiliates
                      FROM tbl_customers

Syntax 2: SELECT *
                   INTO Table_2
                   FROM Table_1

Example 2: SELECT *
                      INTO tbl_affiliates
                      FROM tbl_costumers
This will copy all of table_2 data to table_1

Hope it helps,
Lead Developer at Sports Betting Tech.


Popular posts from this blog

Linked Files in Visual Studio 2010

Advantages and Disadvantages of Decriminalising Privacy

The Path is Already Mapped in Workspace