Insert Update Delete

Use Automart;

Create Table test
(
 testKey int identity(1,1) primary key,
 TestName nvarchar(255)
)

Insert into test (TestName)
Values ('Test One'),
('Test Two'),
('Test Three')

Select * From Test
Select MAX(PersonKey) from Person

Insert into Person (LastName, firstname)
values('Muntz', 'Nelson')


Insert into Customer.RegisteredCustomer(Email, CustomerPassword, PersonKey)
Values ('nelson@gmail.com','nelsonpass', 53)

Insert into Customer.Vehicle(LicenseNumber, 
VehicleMake, 
VehicleYear,
 PersonKey)
Values('456NET', 'VW Beetle','1972', IDENT_CURRENT('Person'))

Select * From Person
Select * From Customer.RegisteredCustomer
Select * From customer.Vehicle

--updates are dangerous
Update Customer.vehicle
Set VehicleYear ='1973'
Where VehicleID =47

Begin Tran --if you explicitly begin a transaction
--you can roll it back if you make a mistake
--other wise there is no undo

Update Person
Set FirstName = 'John'
where LastName='Smith'

Select * From Person

rollback tran --undoes the explicit transaction


--you can update
Update Customer.RegisteredCustomer
Set Email='muntz@gmail.com',
CustomerPassword='muntzpass'
Where PersonKey=53

--change all the values in a table on purpose
Update Customer.AutoService
Set ServicePrice=ServicePrice * 1.05

--this actually won't delete much because
--the records in the parent table person
--have child records in other tables
--you must delete all the children before 
--you can delete the parent
Delete From Person

Begin tran

--delete with a criteria
Delete from customer.RegisteredCustomer
where RegisteredCustomerID=4

Select * From customer.vehicle

commit tran--if all is good, commit the transaction
--and write to the database