Insert, Update, Delete

--Inserts updates and deletes
Use CommunityAssist

--basic insert statement
Insert into Person (LastName, FirstName)
Values ('Bowie', 'David')

--inserting multiple rows
Insert into Person(LastName, FirstName)
Values('Madonna', null),
('Dylan', 'Bob'),
('Gaga', 'Lady')

--inserting into a child table (need to have the foreign key)
Insert into PersonAddress( Street, Apartment, [State], City, Zip, PersonKey)
Values('Somewhere on the Road',null,'Mn','Duluth',null, 54)

Select * From Person

Select LastName, FirstName, Street, [State], City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
Where LastName='Dylan'

Insert into Person(LastName, FirstName)
Values('Young', 'Neil')

--the ident_current(table name) returns the last key generated
--for the named table, though that only works
--on tables with an int identity key
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('neilyoung@harvest.com',IDENT_CURRENT('Person'), 6)


Select * from PersonContact

-- create a new table
Create table Person2
(
 LastName nvarchar(255),
 firstName nvarchar(255)
)

--copy all the lastnames and firstnames
--from Person into the new table
--the new table's columns must have 
--compatible datatypes and there must be
--the same number of fields in the select
--as in the insert
Insert into Person2(Lastname, firstname)
Select lastname, firstname from Person

Select * from Person2

--basic update with criteria
--almost always an update should have
--clear and precise criteria
Update Person2
Set firstName='Jane'
where firstName='Jay'
and LastName='Anderson'

--updating multiple fields
Update Person2
Set LastName='Manning',
firstName='Lewis'
where LastName='Mann'
and firstName='Louis'

--Deletes all records from person2
Delete From Person2

--same as delete
Truncate table Person2

--you can manually control transactions 
--for safety
--a transaction can either be committed (written to database)
--or rolled back
Begin tran
Update Person2
Set LastName='smith'
Where LastName='Carmel'

commit tran
Rollback tran
Select * From Person2

Begin tran
Delete from Person
Delete from PersonAddress
Select * from Person

RollBack tran

Begin tran

Alter table Person2
Drop column firstname

Rollback tran

Update Donation
set DonationAmount=DonationAmount *1.1

Select * from Donation