Insert/Update into a SQL table

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@.yahoo.com> wrote in message
news:1129633654.148651.61570@.g14g2000cwa.googlegro ups.com...
> 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
>
|||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 in message
news:4354d16b$0$134$7b0f0fd3@.mistral.news.newnet.c o.uk...
> 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@.yahoo.com> wrote in message
> news:1129633654.148651.61570@.g14g2000cwa.googlegro ups.com...
>
|||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@.yahoo.com> wrote in message
news:1129641278.899750.254090@.z14g2000cwz.googlegr oups.com...
> 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
>