Saturday, January 8, 2011

How to Create View..

Hi,

Frendzzz

--How to Merge Two Table Concurrenlty, and this data Stored in given Views..
--1st create tow table & insert the data given as the following..
--After tat Create as View as Criteria..

create table FRUITS(
Number int identity(1,1),
Name varchar(25),
Rate numeric(10,2),
Data varchar(50)
);

insert into FRUITS values('Apple Fiesta','125.48','Contains FRUIT PRODUCE')

insert into FRUITS values('Celeriac','58.48','Contains FRUIT AND VEGETABLE PRODUCE')
insert into FRUITS values('Carrots Small','458.26','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Exotic Casava','986.25','Contains FRUIT PRODUCE')
insert into FRUITS values('Herbs Lovage','1025.32','Contains FRUIT AND VEGETABLE PRODUCE')
insert into FRUITS values('Oranges Med','362.25','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Apple Fiesta','256.38','Contains FRUIT PRODUCE')
insert into FRUITS values('Pears Furel','569.86','Contains FRUIT PRODUCE')
insert into FRUITS values('Tomato Plum','45.26','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Turnips','563.18','Contains VEGETABLE PRODUCE')
insert into FRUITS values('Oranges Med','68.59','Contains FRUIT AND VEGETABLE PRODUCE')

update Fruits set Data='Flora India.com' where Data='Contains FRUIT AND VEGETABLE PRODUCE'
update Fruits set Data='Dry Scenario' where Data='Contains FRUIT PRODUCE'

select * from FRUITS

Output :-


create table DRYFRUITS(
Number int identity(1,1),
Name varchar(25),
Rate numeric(10,2),
Data varchar(50)
);

insert into DRYFRUITS values('Assorted Mini Dryfruits','630','Flora India.com')

insert into DRYFRUITS values('Assorted Mini Dryfruits','630','Flora India.com')
insert into DRYFRUITS values('1 Kg Almonds','1150','Flora India.com')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','725','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Almonds','575','Flora India.com')
insert into DRYFRUITS values('Roasted Temptation','1250','Flora India.com')
insert into DRYFRUITS values('Unsatiable Desire','1900','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Pistachio','675','Flora India.com')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','850','Dry Scenario')
insert into DRYFRUITS values('1 Kg Raisins (Kishmish)','650','Dry Scenario')
insert into DRYFRUITS values('1/2 Kg Almonds','1025','Dry Scenario')

select * from DRYFRUITS

Output:



-- How to create view ???
-- Syntax of View Click the following Url..
-- http://msdn.microsoft.com/en-us/library/aa258253(v=sql.80).aspx

Create View View_MergeFruits
As
SELECT     TOP (100) PERCENT dbo.DRYFRUITS.Name AS DryFruits, dbo.DRYFRUITS.Rate AS DryFruits_Rate, dbo.FRUITS.Name AS Fruits,
                      dbo.FRUITS.Rate AS Fruits_Rate, dbo.DRYFRUITS.Data
FROM         dbo.DRYFRUITS INNER JOIN
                      dbo.FRUITS ON dbo.DRYFRUITS.Data = dbo.FRUITS.Data
ORDER BY DryFruits, Fruits

--In that view <View_MergeFruits> all data are Secured Stored...
select * from View_MergeFruits

Output:

This is View Data..Stored Properly in View_MergeFruits