I have the following keys in Consumption:

- Plant
- Material
- Month
- Year

The above are the primary keys in the table and the following are
non-key fields:

- Quantity
- Amount

I have data stored in this table currently but many times I get feeds
which are stored in the table:

Consumption_staging which as the following fields:

Plant
Material
Month
Year
Quantity
Amount

even in the staging table - plant, material, month,year are the keys.

Now I want to update data from the Consumption_staging to the
Consumption table on the following criteria:

If for the same Key fields as in Consumption_Staging if a record is
already present in Consumption table then the record in Consumption
must be updated with the non-key fields else the record from
Consumption_staging must be inserted into the Consumption table.

Greatly appreciate if you could kindly share the SQL code for this
problem I want to just do it possibly just in SQL.

Thanks
Karen

update Consumption
set p = CS.p , ma = CS.ma , mo = CS.mo , yr = CS.yr
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr

insert into Consumption ( p , ma, mo, yr, qu, am )
Select p , ma, mo, yr, qu, am from Consumption_staging CS
left outer join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr
WHERE C.p IS NULL





<karenmiddleol (AT) yahoo (DOT) com> wrote

Ooops, I booboo'd on the update

update Consumption
set qu = CS.qu , am = CS.am
from Consumption_staging CS
inner join Consumption C
ON CS.p = C.p AND CS.ma = C.ma AND CS.mo = C.mo AND CS.yr = C.yr


"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote

Many thanks the update query works fine but the Insert comes back with
this error:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Plant', table
'TestDB.dbo.Consumption'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Plant is part of the Primary key and the system obviously does not
allow nulls. But in the staging table there is no null value in the
Plant field.

But the insert never works please appreciate

Thanks
Karen

The insert gives more errors as follows:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Consumption. Cannot insert
duplicate key in object 'Consumption'.
The statement has been terminated.

Thanks
Karen

It sounds like
1. Your source data has missing data
2. Your source data has duplicate data.

Best solution is to ask whoever sent you the file to fix the data export.

Try this to give you the duplicated records
SELECT p , ma , mo , yr FROM Consumption_staging GROUP BY p , ma , mo , yr
HAVING COUNT(*) > 1


This will give you records with missing data.

SET CONCAT_NULL_YIELDS_NULL ON
SELECT p , ma , mo , yr FROM Consumption_staging
WHERE p IS NULL or ma IS NULL or mo IS NULL or yr IS NULL



HTH


<karenmiddleol (AT) yahoo (DOT) com> wrote