UPDATE/INSERT from VIEW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

UPDATE/INSERT from VIEW

Author

Topic

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-16 : 07:56:56

I have A View as below and destination table as below .

VW_AP
PRC----AUDTDATE(decimal)---AUDTTIME(decimal)
1.25----20100220------------19540011
12.89---20110124------------19535533

TblIns
PRC----AUDTDATE(decimal)---AUDTTIME(decimal)
1.25----20100220------------19540011
12.89---20110124------------19535533

How do I update "TblIns" from vW_Ap if the data is modified and if any new record added to vW_AP then I need to insert to tblIns. Is there any way to compare the tables by AUDTDATE and AUDTTIME. I greatly appriciate any help.
I am using SQL 2008

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 09:12:05

Why do you want to have same data in two tables?

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-16 : 09:27:57

HI
vW_Ap - this going to be in LAN
TblIns - this table will be on web server(hosted outside of LAn)
I can access "TblIns" from my local server.
TblIns - provide the pricing for webserver. So this prices need to updated whenever a changes in vW_Ap (view from ERP).

I APPRICIATE ANY OTHER BEST WAY TO UPDATE/INSERT/DELETE to REMOTE TABLE TblIns from vW_Ap

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 10:33:18

--Update

Update t
set prc=i.prc
from TblIns as t inner join vW_Ap as v on t.AUDTDATE=v.AUDTDATE and t.AUDTTIME=v.AUDTTIME

--Insert
insert into TblIns(prc,AUDTDATE ,AUDTDTIME)
select prc,AUDTDATE ,AUDTDTIME from vW_Ap as v
where not exists(select * from TblIns as t where t.AUDTDATE=v.AUDTDATE and t.AUDTTIME=v.AUDTTIME
)

Madhivanan

Failing to plan is Planning to fail

Vaishu
Posting Yak Master

178 Posts

Posted - 2011-02-16 : 11:31:18

HI
sorry! I failed to mention that AUDTDATE & AUDTTIME is acting like modified date, Modified Time, inserted date and inserted time. So will your sql command still works?. Or please let me know weather I am able to add any Identity colum to the view.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-16 : 11:41:13

What happns when you run the queries?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources