Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to insert only missing rows from another table

Posted on 2008-11-03
8
Medium Priority
?
1,003 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

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 500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

824 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