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

SEO One On One - Web of Links

-
Creating Links, Connecting other sites to yours. The term Web of Links refers to creating multiple sites, each connected to the other one, in order to promote a specific website. This is exactly what a website owner would like, an hour work (and about 50$  investment) which will generate his website 25 links. Web of Links is mainly for indexing purposes and less for getting a higher page rank (PR) in search engines (Google). I'll start by explaining the benefits of the Web of Links method: Let's say you just created a website. You've made all the right things... Listed your site in all the indexes websites, registered to Google Analytics and Google Web Master Tool and even wrote some content pages in order to attract visitors. This is good, the only problem is that Google may have not indexed all you pages yet.  If you're a small new website, this process can take awhile. The Web of Links idea simply makes the linking process smoother. Enter a domain s
Read more