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

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

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

BDD, TDD, and Everything Between

-
Behavior Driven Development, Test Driven Development, and Everything Between What is TDD (Test Driven Development) Test Driven Development was introduced by Kent Beck, in 2003.  This followed the concepts of Extreme Programming, introduced in 1999 with a development experiment done by both IBM and Microsoft. The purpose of the Test Driven Development is to make sure code is clear, tested, and as redundant as possible, by making sure the tests are written first, and code is being added to "fill the blanks".  Every code iteration needs to pass all tests (may those be unit tests, integration tests, data integrity tests, or UI tests). Writing the tests first allow us to see what fails, how, and allow us to visualize the structure of our code, by making sure each test is performed to test a specific, extremely defined sub-section of a feature. Let's assume a "BasicMaths" class, to perform simple mathematics operations. [TestClass]   public   class  Uni
Read more