Inserts Updates Deletes

--Inserts updates and deletes

use CommunityAssist
--basic inserts
Insert into person(Lastname, firstname)
Values ('Donahue', 'Jen')

--multiple rows into the same table
Insert into Person(LastName, firstname)
Values('Brown', 'Jim'),
('Smith', 'Jason'),
('Carleson', 'Jane')

--insert into donor
--insert into person
--insert into personaddress
--insert into personcontact
--insert into donation

insert into Person(LastName, firstname)
Values('Conger','Steve')

--ident-current returns the last identity(autonumber) generated
--in the table you put in the parenthesis-- in this case
--the last person entered

Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values('1201 Broadway','3176D', 'Wa', 'Seattle', '98122', IDENT_CURRENT('Person'))

--contacttypekey 1 is for home phone
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('2065551234', Ident_Current('Person'), 1)

--contactTypekey 6 = email
Insert into PersonContact(ContactInfo, PersonKey, ContactTypeKey)
Values('steve@conger.com', Ident_Current('Person'), 6)

--donation GetDate() returns current date and time
Insert into donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), 200,Ident_current('Person'), 1)

--example with a literal personkey--just enter the number
Insert into donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), 200,55, 1)

--for an existing person not the last person entered
--create a variable to store the key
Declare @person int
--get the key with a select statement
select @Person=personkey from Person 
where lastname='Anderson'
and firstname='Jay'
--use the variable in the insert (all these must be run together)
Insert into donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(), 200,@Person, 1)


--create a table to copy into
Create table PersonB
(
 personkey int,
 lastname nvarchar(255),
 firstname nvarchar(255)
)
Go

--insert with a select. This copies all the records from Person
--into Person b
Insert into Personb(personkey, lastname, firstname)
Select Personkey, Lastname, firstname from Person

--remove on record
Delete from Person
where Personkey=54

--updates change existing records. this changes jay anderson to 
--jason anderson
update Person
Set firstname='Jason'
where Personkey=1

--without a where the the update changes
--all the records in a table
Update PersonB
Set Lastname='Anderson'

Select * from PersonB

--if you manually start and close a transaction you
--can use rollback to undo the command

begin transaction
Update Person
Set Lastname='Smith'

Select * from Person

--this restores the table to its previous state
--and ends the transaction
Rollback transaction



Select * from Person
Update Person
Set Firstname='Jay'
where PersonKey=1

Select * from Person

--commit writes the transaction to the database
commit transaction

--you may want to change several values at once
update Donation
set donationamount = donationamount*1.05

Select * from Donation

Rollback tran
Select * from employee

--will fail because there is no employee 12
--you can't update to a value that would
--violate integrity
Update Donation
set EmployeeKey = 12
where EmployeeKey=1

--won't work because personkey1 has child records
--in other tables
Delete from Person
where personkey=1

--both commands remove all the records from Personb
Delete from Personb
Truncate table Personb

Select * from PersonB

Begin tran
--removes the table from the database
--won't work here because Person has 
--child records
Drop table Person
rollback tran