same table update and insert

Question

 

Hi,

 

Can any one help me how to get this query....

 

This is my sample data..

 

LID Rate RID

1    10    111

2    20    123

3    40    111

4    30    312

5    60    123

 

Result set should be like this on the same table....

 

The result table will be like this

 

LID    Rate    RID

1       10       111

2       20       123

3       40       111

4       30       312

5       60       123

111    50       322

123    80       211

312    30       322

 

The above scenario is, i have to group the RID's and sum of the rates and place in the same table as above what i shown.

Daily I will get some new records and some changed records. i have to update if there is any change records and insert if there are any new records in the same table.

 

Suppose next day with the same LID 111 i got the record and the rate has chnaged to 60, then i have to update at the 111 row and if i got the new record with the new LID some 413,i have to insert that record into the same table.

 

I will get those records some other table. The other table contains the LID and rates and corresponding RID include date also. I will insert records into the above table day by day.

 

Can i get query for this.

 

Regards

sqldev

Answer 1

Something similar to this may help.  Do the updates first then the inserts.

 

I assumed the LID was the Primary Key, and that you would be updating both the rate and rid

 

Code Snippet

-- Main Table -- Asuming LID is the PK

CREATE TABLE #T

(

LID INT

,RATE INT

,RID INT

)

INSERT INTO #T VALUES(1, 10, 111)

INSERT INTO #T VALUES(2, 20, 123)

INSERT INTO #T VALUES(3, 40, 111)

INSERT INTO #T VALUES(4, 30, 312)

INSERT INTO #T VALUES(5, 60, 123)

GO

-- Te table with the updates and inserts

CREATE TABLE #U

(

LID INT

,RATE INT

,RID INT

)

INSERT INTO #U VALUES(1, 110, 111)

INSERT INTO #U VALUES(2, 120, 123)

INSERT INTO #U VALUES(3, 140, 111)

INSERT INTO #U VALUES(4, 130, 312)

INSERT INTO #U VALUES(5, 160, 123)

INSERT INTO #U VALUES(111, 50, 322)

INSERT INTO #U VALUES(123, 80, 211)

INSERT INTO #U VALUES(312, 30, 322)

GO

-- First Update

UPDATE t

SET t.Rate = u.Rate

,t.RID = u.RID

FROM #T t

INNER JOIN #U u

ON t.LID = u.LID

Go

INSERT INTO #T

SELECT LID, RATE, RID

FROM #U

WHERE NOT EXISTS

(

SELECT *

FROM #T

WHERE #T.LID = #U.LID

)

GO

 

GO

SELECT * FROM #T

Go

 

DROP TABLE #T

DROP TABLE #U

 

 

Answer 2

Thanks alot, this one is useful for me