How to insert only missing rows from another table

Hi!

I have two tables: Serials and SerialsNew.
Serials table contains the following columns:
PO
SerialID (PK)
Item
Description

SerialsNew table contains the following columns:
PO
SerialID (PK)
Item

I'm copying data related to certain PO from Serials into SerialsNew with the
following procedure:
INSERT SerialsNew ( PO,  SerialID,  Item)
SELECT PO, SerialID, Item
FROM Serials s
WHERE  EXISTS
 ( SELECT *
    FROM   Serials sn
        WHERE  s.PO = sn.PO
     AND  sn.SerialID = @SerialID )

So, I'm OK till here. Then, if some rows are deleted (or were not yet
available in Serials when previous procedure was run):
Example:
DELETE FROM SerialsNew WHERE SerialID= '10004'
DELETE FROM SerialsNew WHERE SerialID= '10005'
DELETE FROM SerialsNew WHERE SerialID= '10003'

I need to update the SerialsNew table with the missing rows. Let's say if
user inputs 10003 as a parameter, procedure needs to find appropriate PO
from Serials table and accordingly insert missing rows into SerialsNew.
If I run the previous procedure after inserting new rows with the same PO
into Serials table, or delete several rows from SerialsNew table I get the
following error:
Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'PK_SerialsNew'. Cannot insert duplicate
key in object 'dbo.SerialsNew'.

How do I modify the procedure so it works fine also when I need to update
the table with missing rows.

Bellow is the script for creating these tables and some sample data:

/*------------------------------------------------------------------*/
USE [AdventureWorks]
GO
 
-- TABLE A: SERIALS
/****** Object:  Table [dbo].[Serials]    Script Date: 11/03/2008 10:42:58 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Serials](
 [PO] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
 [SerialID] [nvarchar](10) COLLATE Latin1_General_CS_AS NOT NULL,
 [Item] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
 [Description] [nvarchar](100) COLLATE Latin1_General_CS_AS NULL,
 CONSTRAINT [PK_Serials] PRIMARY KEY CLUSTERED
(
 [SerialID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = 
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
-- INSERT SOME SAMPLE DATA
INSERT INTO Serials VALUES ('987A','10001','21000A','Description of item A')
INSERT INTO Serials VALUES ('987A','10002','21000A','Description of item A')
INSERT INTO Serials VALUES ('987A','10003','21000A','Description of item A')
INSERT INTO Serials VALUES ('987A','10004','21000A','Description of item A')
INSERT INTO Serials VALUES ('987A','10005','21000A','Description of item A')
INSERT INTO Serials VALUES ('987A','10006','21000A','Description of item A')
INSERT INTO Serials VALUES ('987B','10007','21000B','Description of item B')
INSERT INTO Serials VALUES ('987B','10008','21000B','Description of item B')
INSERT INTO Serials VALUES ('987B','10009','21000B','Description of item B')
INSERT INTO Serials VALUES ('987B','10010','21000B','Description of item B')
INSERT INTO Serials VALUES ('987B','10011','21000B','Description of item B')
INSERT INTO Serials VALUES ('987B','10012','21000B','Description of item B')
 
 
-- TABLE B: SerialsNew
CREATE TABLE [dbo].[SerialsNew](
 [PO] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
 [SerialID] [nvarchar](10) COLLATE Latin1_General_CS_AS NOT NULL,
 [Item] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
 CONSTRAINT [PK_SerialsNew] PRIMARY KEY CLUSTERED
(
 [SerialID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = 
OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
-------------------------/**/-------------------------
 
-- WHEN USER INPUTS ANY SerialID THIS QUERY WILL INSERT
-- ALL ROWS WITH THE SAME PO AS THE USER INPUT SerialID
INSERT SerialsNew
(
 PO,
 SerialID,
 Item
)
SELECT PO, SerialID, Item
FROM Serials s
WHERE  EXISTS
 ( SELECT *
  FROM   Serials sn
        WHERE  s.PO = sn.PO
     AND  sn.SerialID = @SerialID
 )
-------------------------/**/-------------------------
 
DELETE FROM SerialsNew WHERE SerialID= '10004'
DELETE FROM SerialsNew WHERE SerialID= '10005'
DELETE FROM SerialsNew WHERE SerialID= '10003'
SELECT * FROM SerialsNew
/*------------------------------------------------------------------*/

Open in new window

LVL 1
ZaurbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_PaulCommented:
Can you check your procedure for copying rows across. It seems to me that your code will only copy across rows that already exist in both tables and where the serial is a passed parameter. I think that this should be simplified to just copy across one row if it does not exist.

INSERT SerialsNew
(
 PO,
 SerialID,
 Item
)
SELECT PO, SerialID, Item
FROM Serials s
WHERE sn.SerialID = @SerialID
and NOT EXISTS (select 1 from SerialsNew where SerialID = @SerialID)
0
ZaurbAuthor Commented:
This is how I've made it work.
What do you think?

INSERT	SerialsNew 
		(PO, SerialID,Item)
SELECT	PO,SerialID,Item 
FROM	Serials s 
WHERE	EXISTS 
	(	SELECT *
		FROM   Serials sn
        WHERE  s.PO = sn.PO
	    AND  sn.SerialID = @SerialID
	)
		AND s.SerialID NOT IN 
	(
	SELECT	SerialID
	FROM	SerialsNew sn 
	WHERE	sn.PO = s.PO
	)

Open in new window

0
ZaurbAuthor Commented:
I need to copy all rows that are present in Serials table, but missing in SerialsNew.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

_PaulCommented:
mmm maybe I have not understood your problem. I'm not sure what the EXISTS clause is checking for. It appears to be joining serial  with itself for no reason. Surely you just want the row 'WHERE s.SerialID = @SerialID'

and the NOT IN Clause has a where clause that is not needed. If serialID is the primary key then why do you say 'WHERE sn.PO = s.PO' surely you want never to insert the same SerialID whatever.

Did you try my SQL above?
0
_PaulCommented:
I think that your SQL does work but I think that it is over complicated. I think that my sql does the same thing.
0
ZaurbAuthor Commented:
When I run your query, it inserts only one row at a time while my query inserts all rows. This is the difference.

You see, in my sample Serial table there're 2 orderIDs: 987A and 987B. I need to take all missing SerialIDs from Serials table into SerialsNew table. This is what my SQL statement does.

I've resolved this issue for myself after posting a question and I expect some better advises or proposals since I'm not a professional SQL programmer.

0
_PaulCommented:
If you want to copy all rows that don't exist in the target table then you don't need the serial number as a  parameter. The sql below will do this. I have simply removed the selection criteria for the SerialID

INSERT SerialsNew
(
 PO,
 SerialID,
 Item
)
SELECT PO, SerialID, Item
FROM Serials s
WHERE NOT EXISTS (select 1 from SerialsNew where SerialID = @SerialID)


Best Wishes

Paul
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZaurbAuthor Commented:
your query returns one row at a time. But I actually was writing a query, that returns all the missing rows. Please, see the following examlpe and a query that I use.
I resolved this by using this query:


INSERT SerialsNew
(PO, SerialID,Item)
SELECT PO,SerialID,Item
FROM Serials s
WHERE EXISTS
(SELECT *
FROM Serials sn
WHERE s.PO = sn.PO
AND sn.SerialID = @SerialID
)
AND s.SerialID NOT IN
(SELECT SerialID
FROM SerialsNew sn
WHERE sn.PO = s.PO)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.