Alter one row of a table variable

Research and test the creation of a table variable, insert 3 records into it and change one of the values of a column in only 1 row. Then select all records.

Declare @Frogs table
(
id int primary key identity(1,1) not null
, legs int
, color varchar(36)
, spots int
)
Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)
Insert into @Frogs (legs, color, spots) values (4, 'red', 7)
Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)

select * from @Frogs

UPDATE @Frogs
SET color='green', spots=9
WHERE color='purple' AND spots=22

select * from @Frogs

Delete one record of a table variable

Research and test the creation of a table variable, insert 3 records into it and delete only one of the records. The select all records.

Declare @Frogs table
(
id int primary key identity(1,1) not null
, legs int
, color varchar(36)
, spots int
)
Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)
Insert into @Frogs (legs, color, spots) values (4, 'red', 7)
Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)

select * from @Frogs

DELETE FROM @Frogs
WHERE id=1

select * from @Frogs

Create table variable with 3 columns and 3 rows

Research and test the creation of a table variable, insert 3 records into it, and select all records. Use 3 columns and 3 records.

Declare @Frogs table
(
id int primary key identity(1,1) not null
, legs int
, color varchar(36)
, spots int
)
Insert into @Frogs (legs, color, spots) values (5, 'blue', 3)
Insert into @Frogs (legs, color, spots) values (4, 'red', 7)
Insert into @Frogs (legs, color, spots) values (8, 'purple', 22)

select * from @Frogs

1/19/11 Notes

Use DB
Create Table Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)


mdf - data ROM
ldf - logging

two types of tables, temp tables and table variables
syntax and when to use each type

There are two types of Temp Tables, they are local and global.
Local temp tables have a single # sign as the first character in their names, they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instances of the server.
Global temp tables have a double # sign as the first character in their names, they are visible to any user after they are created, and they are deleted when all users referencing the tables have disconnected from the server.
Local temp table:

Use DB
Create Table #Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

Global temp table:
Use DB
Create Table ##Frogs (
ID int primary key identity(1,1),
,Legs int not null
,Color varchar(8982)
)

A table variable uses the @ symbol as the first character in the name and it deletes when the script you are looking at finishes running. (As long as the script takes to run)

declare @mynumber int
set @mynumber = 3
select @mynumber

Declare @Frogs table;
id int primary key identity(1,1)
, legs int not null
)
Insert into @Frogs (legs) values (@mynumber)
Insert into @Frogs (legs) values (4)

select * from @Frogs

go