Insert Update Delete

--insert update delete
use Automart

--basic insert, inserting two rows
Insert into Person (LastName, FirstName)
values('Jaunes', 'Lindsey'),
('Norton', 'Martin')

--insert with a subquery
Select * From Customer.Vehicle
Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values('EFG123', 'Pontiac', '1969', 
(Select Personkey From Person Where LastName='Norton' and Firstname='Martin'))

--a set of inserts, inserting a new person
--a vehicle, a registered customer
--and a vehicle service
Insert into Person(lastName, firstname)
Values('Ignatius', 'Gonzaga')

--IDENT_CURRENT ('TABLENAME') is a function that returns the last
--identity (autonumber) created in the table named
Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values('123TWS', 'Fiat', '2012',IDENT_CURRENT('Person'))

Insert into Customer.RegisteredCustomer(Email, CustomerPassword, PersonKey)
Values ('gonzaga@gmail.com', 'gpass', IDENT_CURRENT('Person'))

INsert into Employee.VehicleService(VehicleID, LocationID, ServiceDate, ServiceTime)
values(IDENT_CURRENT('Customer.Vehicle'), 2, getDate(), getDate())

Select * from Customer.Vehicle

Select * From Employee.VehicleService

--create a temp table
Create table PersonB
(
    personkey int,
 LastName nvarchar(255),
 FirstName nvarchar(255)
)

--insert all the records from person into personb
Select * from PersonB
Insert into PersonB(personkey, LastName, FirstName)
Select personkey, LastName, firstname from Person

--updates: these are the most dangerous statments
--make sure you have an appropriate where clause for criteria
Select * From customer.vehicle
Update PersonB
Set Firstname = 'Jason' 
Where PersonKey=1

Update Customer.Vehicle
Set VehicleMake='firebird',
LicenseNumber='GFE123'
Where VehicleID=47

--manually begin a transaction
Begin transaction

Update Person
Set LastName='Smith'
where personkey=13

Select * From Personb

--rollback undoes any thing done during the transaction
rollback tran
--commit writes it
Commit tran

Delete from Person where personKey > 20

--removes the actual table and any data in the table
Drop table PersonB
--truncate is basically the same as delete
Truncate table Person