MSSQL Statements Syntax
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.
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 your 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.