Friday, January 28, 2011

Table Variable Create and Inner Join

Below is code on how I created 2 Table Variables and then inner joined the two using a primary key.

 use blog
  
 go
  
 declare @Potatoe_Varieties table
  
 (
  
      Potatoe_ID int identity (1,1) PRIMARY KEY,
  
      Name varchar (40),
  
      Color varchar (10),
  
      Uses varchar (60)
  
 );
  
 Insert into @Potatoe_Varieties
  
 (Name, Color, Uses)
  
 Values
  
 ('Norlands', 'Red', 'Fresh Market Sales')
  
 Insert into @Potatoe_Varieties
  
 (Name, Color, Uses)
  
 Values
  
 ('Russet Burbank', 'Yellow', 'Chips, fries, dehydrated for later use')
  
 Insert into @Potatoe_Varieties
  
 (Name, Color, Uses)
  
 Values
  
 ('Yukon Gold', 'Golden', 'Fresh Market Sales and dehydrated for later use')
  
 declare @Potatoe_size table
  
 (
  
      Potatoe_ID int identity (1,1) PRIMARY KEY,
  
      [weight] varchar (10),
  
      diameter varchar (10),
  
      yield varchar (30)
  
 );
  
 Insert into @Potatoe_size
  
 ([weight], diameter, yield)
  
 Values
  
 ('8oz', '6 inches', '150-300 100lb bags per acre')
  
 Insert into @Potatoe_size
  
 ([weight], diameter, yield)
  
 Values
  
 ('12oz', '7 inches', '250-375 100lb bags per acre')
  
 Insert into @Potatoe_size
  
 ([weight], diameter, yield)
  
 Values
  
 ('7oz', '6 inches', '150-300 100lb bags per acre')
  
 select PV.*, [weight], diameter, yield
  
 from @Potatoe_Varieties PV
  
 inner join @Potatoe_size PS
  
      on PV.Potatoe_ID = PS.Potatoe_ID
  

No comments:

Post a Comment