Update two SQL table columns based on view (SQL Server 2000)

I'm struggling with a SQL question.. We use enterprise manager to communicate with SQL Server 2000.

Background:

I have a table (Table A) that houses salesperson number(slspsn_no) and region and sales info... since there is more than one record for salesperson number and region, i created a view (View A) that groups salesperson number and region.

Now I created a separate table (Table B) that contains two similar columns based off that view in addition to many more.

Columns: slspsn_no, region, January_sales_goals, February_sales_goals, March_sales_goals, etc.

Those monthly sales goals will be managed by a person through access. The only problem is this works great for now, but table A is the one that has slspsn_no and region updated frequently and hence my view.

Question:

Can somebody help me with a SQL command that will update those two columns in Table B based on View A? The challenge is to do this without placing nulls in the monthly sales goals already input.. it would just delete any rows in table B if that salesperson/region combo is no longer in View A, or add an additional row if a new salesperson/region has been created in View A with nulls or zero's for each of the monthly sales goals that can be changed through access later.

If somebody has an idea that would group slspsn_no and region from table A without the need for a view while creating the syntax, I'd be happy to try that to.

Thanks so much!, -开发者_Go百科D

---

What you need is not a separate table but a materialised view.

In SqlServer you create a materialised view by putting an index on a view, which contains the columns you wish to "materialise".

SqlServer keeps it up to date automatically.

• http://technet.microsoft.com/en-us/library/cc917715.aspx

---

Have a look at this example.

You should be able to modify it to suit your specific needs.

set nocount on;

declare @A table(id int, region int, name varchar(90));
insert @A values(1,200,'bob');
insert @A values(2,300,'lee'); -- doesn't exist in @B, should be added
insert @A values(5,300,'mia');
insert @A values(19,300,'tex');
insert @A values(401,400,'gaga'); -- doesn't exist in @B, should be added

declare @B table(id int, region int, goal1 int, goal2 int);
insert @B Values(1,200,8,9);
insert @B Values(5,300,7,7);
insert @B Values(19,300,5,9);
insert @B Values(555,100,1,1);  -- doesn't exist in @A, should be deleted

select * from @B order by id, region

-- insert new @A records into @B
insert @B (id, region)
select id, region from @A where id not in (select id from @B)

-- delete records from @B where not found in @A
delete @B
where id not in (select id from @A)

select * from @B order by id, region