Manipulating Data in Sql server 2008 & Oracle
Manipulating Data
After completing this lesson, you should be able to do the following:
§ Describe each data manipulation language (DML) statement
§ Insert rows into a table
§ Update rows in a table
§ Delete rows from a table
§ Control transactions
A DML statement is executed when you:
§ Add new rows to a table
§ Modify existing rows in a table
§ Remove existing rows from a table
A transaction consists of a collection of DML statements that form a logical unit of work.
INSERT Statement
INSERT INTOMyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO departments(department_id,
department_name,manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
§ Insert a new row containing values for each column.
§ List values in the default order of the columns in the table.
§ Optionally, list the columns in the INSERT clause.
§ Enclose character and date values within single quotation marks.
INSERT Specific Date and Time Values
è Oracle
INSERT INTO employees (employee_id,hire_date, job_id, salary , birth_date)
VALUES (113,SYSDATE, 'AC_ACCOUNT', 6900 , TO_DATE('FEB 3, 1999', 'MON DD, YYYY'));
è Sql Server
INSERT INTO DateTimeTable (DateFiled, TimeFiled, DateTimeFiled)
VALUES (GETDATE(),GETDATE(),GETDATE())
INSERT INTO DateTimeTable (DateFiled, TimeFiled, DateTimeFiled)
VALUES (2012-08-27 , 07:45:45.287 , 2012-08-27 07:45:45.287)
Inserting From One Table To Another
INSERT INTO sales_rep (FirstName, LastName)
SELECT FirstName, LastName FROMemployees
WHERE job_id = 2
UPDATE Statement
UPDATE TableName
SET Column1 = 'Value'
WHEREAnotherColumn = 'AnotherValue'
UPDATE employees
SET department_id =50
WHERE employee_id =113;
Update Table From Another Table
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id =205),
salary = (SELECT salary
FROM employees
WHERE employee_id =205)
WHERE employee_id = 113;
DELETE Statement
DELETE FROM departments
WHERE department_name ='Finance';
DELETE FROM Sales.SalesPerson
WHEREBusinessEntityID IN
(SELECTBusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD >2500000.00)
TRUNCATE Statement
-- Following Statment will empty TestTable
TRUNCATE TABLE TestTable
-- Following SELECT will return TestTable empty
SELECT * FROM TestTable
-- Following SELECT will return TestTable with original data
ROLLBACK
SELECT * FROM TestTable
Database transactions control using COMMIT, ROLLBACK, and SAVEPOINT
§ COMMIT
§ Data changes are saved in the database.
§ The previous state of the data is overwritten
§ You cannot roll back a transaction after a COMMIT
§ Must Use “BEGIN TRAN” Statment
BEGIN TRAN
DELETE FROMTest_Table
WHERE col1 ='2'
COMMIT;
§ ROLLBACK
§ Data changes are undone.
§ Previous state of the data is restored.
§ Locks on the affected rows are released
TRUNCATE TABLETestTable;
ROLLBACK;
§ SAVEPOINT
§ Savepoints offer a mechanism to roll back portions of transactions.
§ To create a savepoint, use the SAVE TRAN or SAVE TRANSACTION command.
§ You must provide a name for the savepoint. This should be a string of up to 32 characters.
BEGIN TRAN;
INSERT INTOTest_Table VALUES ('5');
SAVE TRAN Savept;
§ Release SAVEPOINT
àStep1
BEGIN TRAN;
INSERT INTOTest_Table VALUES ('5');
àStep2
SAVE TRANSavept;
DELETE FROMTest_Table
WHERE col1 ='5'
àStep3
ROLLBACK TRAN Savept;
Summary
Function | Description |
INSERT | Adds a new row to the table |
UPDATE | Modifies existing rows in the table |
DELETE | Removes existing rows from the table |
TRUNCATE | Removes all rows from a table |
COMMIT | Makes all pending changes permanent |
SAVEPOINT | Is used to roll back to the savepoint marker |
ROLLBACK | Discards all pending data changes |
Post a Comment