Avatar of SmashAndGrab
SmashAndGrab
 asked on

SQL Query - is this correct?

Hi,

I have the following SQL Query:


INSERT INTO Archive_E2LTORI SELECT * FROM E2LTORI WHERE LGNUM = '" & prRS("LGNUM") & "' AND TANUM = '" & prRS("TANUM") & "' AND VBELN ='" & Delivery & "' AND VBELN NOT IN(SELECT VBELN from Archive_E2LTORI where VBELN= '" & Delivery & "'

It basically copys data from the LIVE table to the arichive table.

The problem is that I kept getting a a primary key issue - so I then added the "NOT IN" so that the query makes sure that the combo exists before inserting.


My question is:

Is this correct?   Or should I add the addtional fields the the NOT IN part?
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon
Vitor Montalvão

When archiving data you must assure that the data you're going to archive is not archived yet.
The NOT IN clause is just one of the many solutions that you have to achieve this.
SmashAndGrab

ASKER
I think the query should be ..


INSERT INTO Archive_E2LTORI SELECT * FROM E2LTORI WHERE LGNUM = '" & prRS("LGNUM") & "' AND TANUM = '" & prRS("TANUM") & "' AND VBELN ='" & Delivery & "' AND VBELN NOT IN(SELECT VBELN from Archive_E2LTORI LGNUM = '" & prRS("LGNUM") & "' AND TANUM = '" & prRS("TANUM") & "' AND VBELN ='" & Delivery & "')

I think?
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Chris Luttrell

What is your primary key in the Archive_E2LTORI table?
What do you want to do if the record already exists, nothing, just do not insert again?  What if other columns have changed?
Select * is bad practice for production code, and not specifying the columns in the insert.  This will break if someone modifies either table on you.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Vitor Montalvão

SmashAndGrab, a feedback will be appreciated.