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,
Elad,
Lead Developer at Sports Betting Tech.

Comments

Popular posts from this blog

Linked Files in Visual Studio 2010

Protecting Personal Data

SEO One On One - Web of Links