CODEWITHSUNDEEP
sqlsql-server-2008-r2sql-updatesql-insert
Gary Doublé
Gary Doublé

Reputation: 446

Insert or update table from view

I have a view that I'm using to organise a few related tables into one more manageable set of statistics columns (such as purchase / commission information).

I have got all the calculations sorted now on the view but because there are many calculated columns and LOTS of data (nearly 1m records) the view struggles to perform well.

As an example accessing the view without modification returns results, but simply doing a count(*) on one column selected from that view takes a very long time.

My idea is to take the data of the view and render it into a "real" statistics table that is modelled based on the views columns.

My question is, how do I insert or update columns from the view directly into this new table?

I need to be able to INSERT into the new table if an entry does not match the PK and UPDATE the existing row if it does because the view data may change due to processes effecting the values of its columns, I.E: Someone cancelling an order so a refund will be made then the view will show the statistics as now having more money refunded, which I will then need updating in my storage table.

Also, due to code complexity and time constraints I cannot do this at the point of action so it will called periodically.

I hope this makes sense.

Upvotes: 0

Views: 7729

Answers (1)

saamorim
saamorim

Reputation: 3905

From what I understand, you're best option, considering Sql Server 2008 is to use the Merge Command http://technet.microsoft.com/en-us/library/bb510625.aspx

where you can write something like: 

MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
    UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)

By the way, 1 million records shouldn't be a problem if you have a good database structure and maintenance. Check your indexes, statistics and histograms to see if they are still valid. Also run tunning advisor in order to see if your indexes are good.

Upvotes: 1

Related Questions