Solved

How to insert only missing rows from another table

Posted on 2008-11-03
8
959 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now