Copy SQL records from one table to another without duplicating existing records

Hello Experts;

I understand how to copy records from one SQL table to another but how can I repeat the query copying only new records. My current query copies all the records and inserts them into the target table but if I repeat the query it copies all the records again into a new set of rows...

INSERT INTO SRSCustom1.dbo.ORDERS
(ORD_ID,ORD_STATUS,ORD_DEPOSIT,ORD_TOTAL,ORD_CUSTID,ORD_AUTHBUYER,ORD_SALESID)

SELECT
ORD_ID,ORD_STATUS,ORD_DEPOSIT, ORD_TOTAL,ORD_CUSTID,ORD_AUTHBUYER,ORD_SALESID

FROM SRSPRM2.dbo.ORDERS

The below query works for a single column but I can't figure out how to make it work for multiple columns...

SELECT * FROM SRSPRM2.dbo.ORDERS
WHERE ORD_STATUS
NOT IN
(SELECT ORD_STATUS
FROM SRSCustom1.dbo.ORDERS)

I need help in structuring a query that will copy records from the host table to the target table, skipping records that already exist in the target table.

Thank you,
Visionetv
visionetvAsked:
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.

Mike EghtebasDatabase and Application DeveloperCommented:
SELECT * FROM SRSPRM2.dbo.ORDERS
WHERE ID
NOT IN
(SELECT ID
FROM SRSCustom1.dbo.ORDERS)

Note:
SRSPRM2.dbo.ORDERS. ID    is PK in SRSPRM2.dbo.ORDERS and it may or not be a identity column.

but:

SRSCustom1.dbo.ORDERS.ID     is not PK in SRSCustom1.dbo.ORDERS and it may not be a identity column.
0
Mark BullockQA Engineer IIICommented:
Add a WHERE clause with the columns you want to match on.
SELECT * FROM SRSPRM2.dbo.ORDERS o1
WHERE ORD_STATUS
NOT IN 
(SELECT ORD_STATUS 
FROM SRSCustom1.dbo.ORDERS o2
where o2.column1=o1.column1
and o2.column2=o1.column2
etc.)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's because you aren't using the PK. Instead you're using status column that don't define the uniqueness of a record.
I also prefer the NOT EXISTS instead of NOT IN (only for reading purpose):
INSERT INTO SRSCustom1.dbo.ORDERS 
 (ORD_ID,ORD_STATUS,ORD_DEPOSIT,ORD_TOTAL,ORD_CUSTID,ORD_AUTHBUYER,ORD_SALESID)
 SELECT 
 ORD_ID,ORD_STATUS,ORD_DEPOSIT, ORD_TOTAL,ORD_CUSTID,ORD_AUTHBUYER,ORD_SALESID
FROM SRSPRM2.dbo.ORDERS O1
WHERE NOT EXISTS (SELECT 1 
                FROM SRSCustom1.dbo.ORDERS O2
                 WHERE O2.ORD_ID = O1.ORD_ID)

Open in new window

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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

visionetvAuthor Commented:
Vitor;

Thank you, exactly the query structure I needed.

Thanks again,
Visionetv
0
Mike EghtebasDatabase and Application DeveloperCommented:
visionetv,

My original solution seems to be okay even without WHERE O2.ORD_ID = O1.ORD_ID which seems unnecessary.

Did you try my solution at all?

Mike
0
visionetvAuthor Commented:
Mike;

I did... the structure you suggested, as I understood it, was a query I wrote before my request for help. I received the error: "An expression of non-Boolean type specified in a context where a condition is expected, near ',' " which is typically invalid syntax.

I may have misunderstood your solution and structured the query incorrectly from your post, if so my apologies. Here is the entire query using the structure I thought you suggested for which I received the same error.

SELECT * FROM SRSPRM2.dbo.ORDERS
 WHERE ORD_ID,ORD_STATUS,ORD_DEPOSIT,
ORD_TOTAL,ORD_CUSTID,ORD_AUTHBUYER,ORD_SALESID,
ORD_PrimaryID,ORD_SALESDATE,ORD_DATEDUE,ORD_INVOICENO,
ORD_NAME,ORD_CASH,ORD_CHECK,ORD_CHARGECARD,ORD_ONACCOUNT,
ORD_PURCHASEORDR
 NOT IN
 (SELECT ORD_ID,ORD_STATUS,ORD_DEPOSIT,
ORD_TOTAL,ORD_CUSTID,ORD_AUTHBUYER,ORD_SALESID,
ORD_PrimaryID,ORD_SALESDATE,ORD_DATEDUE,ORD_INVOICENO,
ORD_NAME,ORD_CASH,ORD_CHECK,ORD_CHARGECARD,ORD_ONACCOUNT,
ORD_PURCHASEORDR)

In contrast I was able to adapt Vitor's solution this morning without issue. Thank you for your time, and again my apologies if I misunderstood your post.

Visionetv
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
Query Syntax

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.