Add a column to a view from OpenQuery?
-
SELECT *
into #v_saleproperty
FROM OPENQUERY(Server1, 'Select city, MLS_nbr from California.dbo.v_HomeSales')
SELECT city
from #v_saleproperty
WHERE EXISTS (select *
from v_homes
WHERE home_id = #v_saleproperty.MLS_nbr)
After running these queries, I want to add a city column to the view v_homes with #v_saleproperty.city data. What is the next query I write?
Thanks for feedback.
-
You're working backwards... do the work from the other server, everything will be easier that way.
-
thanks for the reply. the view I am altering is on server 2 though.
-
Can you expand a bit more on the task at hand??
-
On server2 I have a view v_homes that I need to add a city column which is populated from server1.v_HomeSales.city. hope that makes sense.
-
No because a view is not a table, you can add a column to a table but you only add a column in the select of the view (view definition).
Are you talking about indexed views that would need access on some remote server data??
-
okay sorry. left out that step. after i alter the table to add the city column with the data from server1, i would then alter the view by selecting from the table.
so I guess my question is how do I add the city column with the data from server1 to the table homes?
-
Do they contain the same data?
Can I see some sample data of matching rows and the ddl?
-
I have inserted my recent comments in green below.
Thanks so much for your help! (and patience
)SELECT *
into #v_saleproperty
FROM OPENQUERY(Server1, 'Select city, MLS_nbr from California.dbo.v_HomeSales')
SELECT city
from #v_saleproperty
WHERE EXISTS(select * from Server2.California.dbo.dim_homes
WHERE home_id = #v_saleproperty.MLS_nbr)
/*getting only the #v_saleproperty.mls_nbrs that match in dim_homes.home_id.
the above Select statement returns 1966 rows
#v_saleproperty.mls_nbrs contains 4765 records
dim_homes contains 1968 records
*/
/* This is where the next query would go to alter the table dim_homes to add the city column data . I am not sure how to do this part.
/*This last query would be to alter the view to reflect the change to the table dim_home.*/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER view v_dim_homes
as
SELECT * FROM dim_homes
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
How do you intend to keep the table syncronized over time??
-
Probably a sceduled dts job.
-
Hmm, I think you should look into replication for this )obviously not the only way, but that's the way I'd look into as it's all automatic).
-
Probably a scheduled DTS job.
-
oops didn't mean to post my reply twice.
Remi, thanks for all help and posts.
-
HTH.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply