Combine 2 tables without Duplicates
I have two SQL tables SerialItems1 and SerialItems2 with the following columns
Ser_Number,
Ser_InvoiceDate,
Ser_ReceivedDate,
Ser_CustNbr,
Ser_SKU,
Ser_ManCode,
Ser_InvNbr,
Ser_Price
I want to combine SerialItems1 with SerialItems2 eliminating duplicate records, what would be the best way to accomplish this?
I tried the following query
INSERT INTO SerialItemsAll
SELECT * from SerialItems1
UNION
SELECT * from SerialItems2
but if my understanding is correct with the UNION all the data must be exact to capture all records. I need this to be accurate. Any help would be greatly appreciated
Ser_Number,
Ser_InvoiceDate,
Ser_ReceivedDate,
Ser_CustNbr,
Ser_SKU,
Ser_ManCode,
Ser_InvNbr,
Ser_Price
I want to combine SerialItems1 with SerialItems2 eliminating duplicate records, what would be the best way to accomplish this?
I tried the following query
INSERT INTO SerialItemsAll
SELECT * from SerialItems1
UNION
SELECT * from SerialItems2
but if my understanding is correct with the UNION all the data must be exact to capture all records. I need this to be accurate. Any help would be greatly appreciated
Éric Moreau
UNION will eliminate the duplicates. If you want to keep all records, you need to use UNION ALL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To be more accurate as you asked,
INSERT INTO SerialItemsAll
SELECT * from SerialItems1
UNION
SELECT * from SerialItems2
Would work well, if you mention the column names in the query to avoid confusions.
INSERT INTO SerialItemsAll ( col1, col2, col3)
SELECT ( col1, col2, col3) from SerialItems1
UNION
SELECT ( col1, col2, col3) from SerialItems2
<< but if my understanding is correct with the UNION all the data must be exact to capture all records >>
For UNION to work, No of columns in both the queries should be the same and the next one is that the order in which you specify in the first query should be the same. And one more thing is that Datatypes should match between the two SELECT statements.
INSERT INTO SerialItemsAll
SELECT * from SerialItems1
UNION
SELECT * from SerialItems2
Would work well, if you mention the column names in the query to avoid confusions.
INSERT INTO SerialItemsAll ( col1, col2, col3)
SELECT ( col1, col2, col3) from SerialItems1
UNION
SELECT ( col1, col2, col3) from SerialItems2
<< but if my understanding is correct with the UNION all the data must be exact to capture all records >>
For UNION to work, No of columns in both the queries should be the same and the next one is that the order in which you specify in the first query should be the same. And one more thing is that Datatypes should match between the two SELECT statements.