Solved

How to insert only missing rows from another table

Posted on 2008-11-03
8
964 Views
Last Modified: 2008-12-05
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

0
Comment
Question by:Zaurb
  • 4
  • 4
8 Comments
 

Expert Comment

by:_Paul
ID: 22868022
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
 
LVL 1

Author Comment

by:Zaurb
ID: 22868058
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
 
LVL 1

Author Comment

by:Zaurb
ID: 22868079
I need to copy all rows that are present in Serials table, but missing in SerialsNew.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Expert Comment

by:_Paul
ID: 22869197
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
 

Expert Comment

by:_Paul
ID: 22869213
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
 
LVL 1

Author Comment

by:Zaurb
ID: 22869324
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
 

Accepted Solution

by:
_Paul earned 125 total points
ID: 22875052
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
 
LVL 1

Author Comment

by:Zaurb
ID: 22992217
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question