Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2012 : Transact-SQL - Data Manipulation Language (part 2)

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
11/30/2014 8:29:22 PM

INSERT

The INSERT DML statement allows users to write data into a database. A simplified example of the syntax follows:

INSERT <table_name> [(column list)]
VALUES( { DEFAULT | NULL | <expression> } [ ,...n] )

where the parameters are defined as follows:

  • <table_name> is the table that you want to insert data into.
  • <column list> is a list of specific columns you want to insert data into. This is optional.
  • <expression> is the actual data that you want to insert. The type of data depends on the data type that is defined for the particular column.
Basic INSERT Queries

The following sample script will add more pets to the Pets table:

INSERT INTO Pets (pet_id, pet_name, pet_weight, MicroChipID)
 VALUES (10,'Roxy',7,'1A8AF59'),
(11,'Champ',95,'81CB910'),
(12,'Penny',80,'C710A6B')

Alternatively, you could have omitted the specific columns you wanted to insert. If you do this, SQL Server will assume that the first value will be for the first column definition, the second value entered will be for the second columns definition, and so on. For example, since you are inserting information into all the columns in the Owners table, you could issue this statement and omit owner_id, pet_id, and owner_name from the statement:

INSERT INTO Owners VALUES (4,5,'Frank')
Identity and Sequences

When the Pets table was defined, a primary key column for pet_id was created. Usually, a table has a primary key column that uniquely identifies the information for that specific row of data. Up until now, when an INSERT statement was given, we supplied this primary key. In a real-world application, the next available primary key might not be known, or it may require some T-SQL execution to obtain the next value. This is one of the scenarios where the IDENTITY property of the CREATE TABLE statement can help with automatically generating a value for the new row. Since we already have the Pets table, to use the INDENTITY property, we would have to drop the table and create it. To help illustrate the property create a new table called PetFood as follows:

CREATE TABLE PetFood
(pet_food_id int IDENTITY(1,1),
 food_name varchar (20));

Now, as data is inserted into the PetFood table, you do not need to specify the pet_food identity, as shown by the following T-SQL statement:

INSERT INTO PetFood VALUES('Lamb and Rice'),('Chicken'),('Corn')

To view the table issue a SELECT * FROM PetFood statement. This will yield the following result:


pet_food_id   food_name

1             Lamb and Rice

2             Chicken

3             Corn

Notice that you didn’t have to figure out what the next available pet_food_id was when you inserted the data into the table.

IDENTITY is a powerful and useful property, but it is also limited in some areas. Perhaps the biggest issue with IDENTITY comes with its restriction to a single table. It is hard to share a common identity value across multiple tables in a single transaction. This is because the identity value is created after the insert. There are some ways to get around this, but a much better solution would be to use a SEQUENCE object. A SEQUENCE object can easily be used by more than one table because it’s not bound to any particular table; rather, it’s a separate independent object. To create a SEQUENCE object, use the CREATE SEQUENCE statement as follows:

CREATE SEQUENCE PetFoodIDSequence
    AS INT
    START WITH 1
    INCREMENT BY 1

Let’s drop and re-create our PetFood table so we can use the new PetFoodIDSequence object:

DROP TABLE PetFood
GO
CREATE TABLE PetFood
(pet_food int DEFAULT (NEXT VALUE FOR PetFoodIDSequence),
 food_name VARCHAR (20));
GO

Now, we can insert our pet food as before using the INSERT statement:

INSERT INTO PetFood (food_name) VALUES('Lamb and Rice'),('Chicken'),('Corn')

Running a SELECT statement on the PetFood table will yield the same results as when we used the IDENTITY property.

UPDATE

The UPDATE DML statement allows the user to modify existing data within the database. A simplified definition follows:

UPDATE <object>
SET { column_name = { <expression> | DEFAULT | NULL }
[ WHERE <search_condition> ]

where the parameters are defined as follows:

  • <object> is the table or view that is being updated.
  • column_name is the name of the column of data that will be updated.
  • <search_condition> is used to filter or scope the list of rows to update.

As an example, let’s update the Owners table and change the ownership of dogs owned by Frank to Courtney. You can do this with the following UPDATE statement:

UPDATE Owners SET owner_name='Courtney' WHERE owner_name='Frank'

The WHERE clause is optional. However, if you do not specify a search condition, you will update every row. So, unless updating all rows is what you intend to do, you should always specify a WHERE clause.

DELETE

The DELETE DML statement allows users to remove data from the database. A simplified definition follows:

DELETE FROM <table_source> [ WHERE <search_condition> ]

where the parameters are defined as follows:

  • <table_source> specifies the table that you want to remove items from.
  • <search_condition> is used to scope the amount of rows that will be deleted.

The following example uses the DELETE statement to remove the pet named Penny:

DELETE FROM Pets WHERE pet_name = 'Penny'

The WHERE clause is optional. If you omit it, you will delete all rows in the specified table. Be very careful about omitting it. Omitting the WHERE clause and inadvertently deleting all data from a table is a common mistake. It’s a mistake that you truly want to avoid making.

Other -----------------
- SQL Server 2012 : Transact-SQL - The VetClinic Sample Database Revisited, Data Types
- Microsoft Sharepoint 2013 : Claims Authentication and Oauth - Server-to-Server Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 3) - App Authentication - App Catalog App Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 2) - App Authentication - SharePoint Store App Authentication
- Microsoft Sharepoint 2013 : Application Authentication (part 1) - Cloud App Model, OAuth
- Microsoft Sharepoint 2013 : User Authentication (part 4) - Using Claims-Based Identity - Federated User Authentication Process
- Microsoft Sharepoint 2013 : User Authentication (part 3) - Using Claims-Based Identity - Understanding the User Authentication Process and Authentication Providers
- Microsoft Sharepoint 2013 : User Authentication (part 2) - Using Claims-Based Identity
- Microsoft Sharepoint 2013 : User Authentication (part 1) - Claims-Based Identity
- Microsoft Sharepoint 2013 : What’s New with Claims and Authorization?
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
Trailers Game
- The Banner Saga 2 [PS4/XOne/PC] PC Launch Trailer
- Welkin Road [PC] Early Access Trailer
- 7th Dragon III Code: VFD [3DS] Character Creation Trailer
- Human: Fall Flat [PS4/XOne/PC] Coming Soon Trailer
- Battlefleet Gothic: Armada [PC] Eldar Trailer
- Neon Chrome [PS4/XOne/PC] PC Release Date Trailer
- Rocketbirds 2: Evolution [Vita/PS4] Launch Trailer
- Battleborn [PS4/XOne/PC] 12 Min Gameplay Trailer
- 7 Days to Die [PS4/XOne/PC] Console Trailer
- Total War: Warhammer [PC] The Empire vs Chaos Warriors Gameplay Trailer
- Umbrella Corps [PS4/PC] Mercenary Customization Trailer
- Niten [PC] Debut Trailer
- Stellaris [PC] Aiming for the Stars - Dev. Diary Trailer #1
- LawBreakers [PC] Dev Diary #4: Concept Art Evolutions
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer