asked on

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

Mike Eghtebas

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.

Mark Bullock

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

visionetv

ASKER

Vitor;

Thank you, exactly the query structure I needed.

Thanks again,
Visionetv

Mike Eghtebas

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

visionetv

ASKER

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