Inserts, Updates, Deletes

use CommunityAssist

--basic insert statement
Insert into Person(Lastname, firstname)
Values('Bloggs','Earnest')

--insert several rows at once 
Insert into Person(Lastname, firstname)
Values('Higgens','Henry'),
('Newton', 'Isaac'),
('Holmes', 'Sherlock')

--create a table patterned after the person table
--for our experiments
Create Table PersonB
(
 personKey int identity(1,1) primary key not null,
 Lastname nvarchar(255) not null,
 FirstName nvarchar(255)
)

--insert with a select clause
Insert into PersonB(LastName, firstName)
Select lastname, firstname from Person

--manually begin a transaction
--this gives you control over what happens
--and lets you have the possibility of an undo
--via rollback--but does hold the table in a lock
--until you are done.
begin transaction

--basic update statement
--if done without the where clause
--will update everything
Update Personb
Set Firstname='Jason',
Lastname='Smith'
Where personKey=1

--undoes the current transaction
rollback transaction

--commits the transaction to the database
--you can only do one of the two
Commit Tran

--deletes all records from a table
truncate table Personb

Select * From Personb

Select * from Donation

--update to increase all donations by 10%
Begin tran
Update Donation
Set DonationAmount=Donationamount * 1.1

Rollback tran

--delete all records from PersonAddress
--make sure byou begin a transaction first
Delete from PersonAddress

Select * from PersonAddress

--this deletes the persons we inserted
--generally a delete should have a where clause
Delete From Person
Where Personkey >=52