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
>

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
Karenupdate 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.googlegroups.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.co.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.googlegroups.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.googlegroups.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
>

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
Karenupdate 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.googlegroups.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.co.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.googlegroups.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
> >
>|||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.googlegroups.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
>

Insert statements, primary keys and relationships

I have a number of tables (30+) all collecting info about societies. I have a primary key (soc_ID) in all my tables and a number of one-to-one relationships. All the tables are joined.

Q1 : If I add into the first row in one table, do I HAVE to insert the PK in EVERY other table its related to? Q2:is it wise to have foreign keys nullable?
My MS SQL SERVER statement::

insert into society(soc_id) values ('1');

ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Society_EDU_materials_targeted". The conflict occurred in database "mem_soc", table "dbo.EDU_materials_targeted", column 'soc_id'.
The statement has been terminated.

I understand the error. Is there a short way to insert the PK in all related tables automatically otherwise my insert statement will be HUGE.
This method seems REALLY long winded...I'm 99% sure that you've got the primary and foreign keys reversed between your EDU_materials_targeted and society tables. At least as I understand it, the soc_id should be the primary key in the society table, and it should be the foreign key in the dbo.EDU_materials_targeted table.

A foreign key should allow NULL values if the relationship has a cardinality of "1 to zero or more". In other words, if the child table might not have a matching row in the parent table, then the FK should allow NULL values.

Normally I'd move this discussion to the Microsoft SQL Server (http://www.dbforums.com/forumdisplay.php?f=7) forum, but since it still is a relatively pure SQL issue I'm Ok with leaving it here in the SQL forum.

-PatP

-PatP|||Thanks a lot! You're 100% correct!

Insert Primary Keys to all tables in a database

I have a database of 300 tables that needed to insert Primary Key:
1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
RPT_NAME.
Is there a fast way to insert it once and for all rather than inserting
it one by one?
Another issue is to change the width of field named COST_CENTR of
VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Currently using MsSQL 2K. Help is greatly appreciated. ThanksYou can speed this up by scripting.
You can derive every table and column name from various system calls, e.g
use informationschema for tables and
exec sp_columns 'myTable' will list the column names.
You could add some logic to check the coloumn names and then perform an
action , such as add PK
A similar process as above for your second problem - check for column
COST_CENTR and do an ALTER TABLE to widen the col width
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"ymcj" <june.ymc@.gmail.com> wrote in message
news:1145520225.026547.254790@.u72g2000cwu.googlegroups.com...
> I have a database of 300 tables that needed to insert Primary Key:
> 1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
> 2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
> RPT_NAME.
> Is there a fast way to insert it once and for all rather than inserting
> it one by one?
> Another issue is to change the width of field named COST_CENTR of
> VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
> Currently using MsSQL 2K. Help is greatly appreciated. Thanks
>|||On 20 Apr 2006 01:03:45 -0700, ymcj wrote:

>I have a database of 300 tables that needed to insert Primary Key:
>1.Set ENO as PRIMARY KEY in all tables that contains the field ENO.
>2. Set RPT_NAME as PRIMARY KEY in all tables that contains the field
>RPT_NAME.
Hi ymcj,
This is a very strange request. If alll tables share the same primary
key, then why is your data spread out over 300 tables? The idea of
normalization is to use different tables for data that needs DIFFERENT
keys.
Can you elaborate a bit on what you're trying to achieve and why?
(snip)
>Another issue is to change the width of field named COST_CENTR of
>VARCHAR 10 to 12 in all tables that has it. ( maintain the contents )
Execute the following SQL in Query Analyzer (using the results to text
option instead of the results to grid option!!)
SELECT 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME + '
' + 'ALTER COLUMN ' + COLUMN_NAME + ' varchar(12)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'COST_CENTR'
Check the result. If it looks okay, select it, copy and paste it to the
query window and execute it.
Test this on a test database first. After that, make sure that you
perform this task during scheduled down time, and make sure that you
have a good backup.
Hugo Kornelis, SQL Server MVP