7. Modifying Data

INSERT-: Insert new data into tables.
         INSERT VALUES-: INSERT INTO Production.UnitMeasure
                         (UnitMeasureCode,Name,ModifiedData)
                         VALUES
                         ('FT2','FEET2','20080414'),('FT3','FEET3','20080415')
         INSERT SELECT-: CREATE TABLE dbo.EmployeeSales
                         ( DataSource varchar(20) NOT NULL,
                           BusinessEntityID varchar(11) NOT NULL,
                           LastName varchar(40) NOT NULL,
                           SalesDollars money NOT NULL
                          );   
                          INSERT INTO dbo.EmployeeSales
                          SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
                          FROM Sales.SalesPerson AS sp
                          INNER JOIN Person.Person AS c
                          ON sp.BusinessEntityID = c.BusinessEntityID
                          WHERE sp.BusinessEntityID LIKE '2%'
                          ORDER BY sp.BusinessEntityID, c.LastName;
                         
UPDATE-: Modify data in table.
         All columns in the row are affected by this statement.
         Always include a WHERE clause to limit the no. of rows being updated.
         e.g. UPDATE Sales.SalesPerson
              SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL
              WHERE TerritoryID=1;
                         
DELETE-: DELETE FROM <TABLE>-: Deletes all the rows from the table.
         So you should always use a WHERE clause.
         Note-: Use TRUNCATE TABLE to remove all the rows from a table (cause it is faster and uses less system and transaction log resources)
         e.g. SELECT TOP 25 LastName, FirstName INTO DeleteExample FROM Person.Person
              select * from DeleteExample
              DELETE FROM DeleteExample
              INSERT INTO DeleteExample SELECT TOP 25 LastName, FirstName FROM Person.Person
              select * from DeleteExample
              DELETE FROM DeleteExample WHERE LastName='Abercrombie' AND FirstName='Kim'