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

Post a Comment

Popular posts from this blog

Linked Files in Visual Studio 2010

-
Image
File Linking, File Sharing and Shared Projects. You know how it feels to have shared files on VSS? Gooood . Actually it felt so good that my beloved CTO (who said something about promotion?) switched to TFS just 4-5 months ago.  And ever since - I cry myself to sleep every night. Instead of one file, shared between 5,6,7 or 10 projects, we now have a copied file for each project.  This is due to Microsoft "Best Practice" which says - No Shared Files . Meaning...? If I have a JavaScript file called Arrays.js.  It's all purpose is to handle arrays in JavaScript.  This file exists in 6 different projects, which mean, that if I have another addition to this small JavaScript library, I have to copy it to 6 different files in 6 different projects... JUST SHOOT ME!!! Finally, I have the fix we've all been waiting for, courtesy of VS 2010: "Add File As Link" How to Link Files to Different Projects: Let’s say we have 2 completely dif
Read more

Protecting Personal Data

-
Protecting personal data can be overwhelming, but it is not impossible. There are highly secure tools both online and offline to protect personal data. Shielding personal data can be logical, highly secure as well as inexpensive. Protecting Personal Data Offline   Physically lock your financial records and personal documents in a safe place in your home. Purchase an inexpensive fire-proof safe that can be stored in a secure closet, built in your floor or wall.   A good fireproof safe costs from $100 to $3000 (Sears, 2013). Protect your wallet and or purse in a desk drawer at work. Limit what you carry when you go out. Never keep your social security card in your wallet; lock it up. When filling out forms in the workplace, the doctor’s office, or your child’s school ask how your information will be safeguarded. If you do not have to fill out every little detail of your life, leave that portion blank. Ask for the consequences of not providing specific information. Shr
Read more

Cloud Computing Advantages and Disadvantages

-
Cloud computing was introduced this week as a giant client server environment. Analyze the advantages and disadvantages of computing on the Internet. Cloud computing refers to anything that is involved in delivering host-based services on the Internet. The services are of three types: IaaS (Infrastructure-as-a-Service), PaaS (Platform-as-a-Service), and SaaS (Software-as-a-Service). The term ‘cloud computing’ is derived from the symbol of the cloud that often describes the Internet in flowcharts and diagrams.  Recently, cloud computing has become one of the most hyped information technology topics of the decade (Enslin 2012). With cloud computing one can access documents and applications from any part of the world. This frees you from being bound to your computer at home. Miller says that cloud computing is definitely not for everyone (Miller 2009). There are advantages and disadvantages to this type of internet computing. While analyzing the advantages, Miller says compu
Read more