"GJ" <GJ@discussions.microsoft.com> wrote in message
news:F075C5B6-85C6-4878-9F18-827CBCD7B2B8@microsoft.com...
> Hi,
> I am not sure if the 'subject' reflects the problem correctly. But I will
> try to explain it here:
>
> I have one view DB1.VIEW1 in Database DB1. The query in VIEW1 is a
> multiple
> join of different tables and views in DB1 and other databases. On
> executing
> VIEW1, I get the output result as:
> Column1 Column2 Column3
> DB1Column1Value DB1Column2Value DB1Column3Value
>
> Similary, I have DB2.VIEW2 that has EXACTLY the same number of columns and
> column names and column types as DB1.VIEW1 columns.
> Column1 Column2 Column3
> DB2Column1Value DB2Column2Value DB2Column3Value
>
> If I join DB1.VIEW1 and DB2.VIEW2, I get the output as:
> Column1 Column2 Column3 Column1 Column2 Column3
> DB1C1V DB1C2V DB1C3V DB2C1V DB2C2V DB2C3V
>
> But instead of getting the final output as all columns from VIEW1 and then
> all columns from VIEW2, I would like to keep only 3 columns and add the
> resultant rows of VIEW2 below the resultant rows of VIEW1. It should be
> like
> this:
> Column1 Column2 Column3
> DB1Column1Value DB1Column2Value DB1Column3Value
> DB2Column1Value DB2Column2Value DB2Column3Value
>
> I tried using JOIN and also using:
> INSERT INTO DB1.dbo.VIEW1 (Sales, Shipping, ProductCost)
> SELECT DB2.Sales AS Sales, DB2.Shipping AS Shipping, DB2.ProductCost AS
> ProductCost
> FROM DB2.dbo.VIEW2
>
> but to get the error:
> 'Update or insert of view or function VIEW1 failed because it contains a
> derived or constant field'
>
> Any help is appreciated. Thank you in advance.

"oj" wrote:

> Michael is correct.
>
> e.g.
> select col1,col2,col3
> from view1
> union all
> select col1,col2,col3
> from view2
>
>
> --
> -oj
>
>
> "GJ" <GJ@discussions.microsoft.com> wrote in message
> news:F075C5B6-85C6-4878-9F18-827CBCD7B2B8@microsoft.com...
> > Hi,
> > I am not sure if the 'subject' reflects the problem correctly. But I will
> > try to explain it here:
> >
> > I have one view DB1.VIEW1 in Database DB1. The query in VIEW1 is a
> > multiple
> > join of different tables and views in DB1 and other databases. On
> > executing
> > VIEW1, I get the output result as:
> > Column1 Column2 Column3
> > DB1Column1Value DB1Column2Value DB1Column3Value
> >
> > Similary, I have DB2.VIEW2 that has EXACTLY the same number of columns and
> > column names and column types as DB1.VIEW1 columns.
> > Column1 Column2 Column3
> > DB2Column1Value DB2Column2Value DB2Column3Value
> >
> > If I join DB1.VIEW1 and DB2.VIEW2, I get the output as:
> > Column1 Column2 Column3 Column1 Column2 Column3
> > DB1C1V DB1C2V DB1C3V DB2C1V DB2C2V DB2C3V
> >
> > But instead of getting the final output as all columns from VIEW1 and then
> > all columns from VIEW2, I would like to keep only 3 columns and add the
> > resultant rows of VIEW2 below the resultant rows of VIEW1. It should be
> > like
> > this:
> > Column1 Column2 Column3
> > DB1Column1Value DB1Column2Value DB1Column3Value
> > DB2Column1Value DB2Column2Value DB2Column3Value
> >
> > I tried using JOIN and also using:
> > INSERT INTO DB1.dbo.VIEW1 (Sales, Shipping, ProductCost)
> > SELECT DB2.Sales AS Sales, DB2.Shipping AS Shipping, DB2.ProductCost AS
> > ProductCost
> > FROM DB2.dbo.VIEW2
> >
> > but to get the error:
> > 'Update or insert of view or function VIEW1 failed because it contains a
> > derived or constant field'
> >
> > Any help is appreciated. Thank you in advance.
>
>