Combine 2 tables without Duplicates

Posted on 2009-02-19
Medium Priority
Last Modified: 2012-06-27
I have two SQL tables SerialItems1 and SerialItems2 with the following columns
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
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
Question by:skull52
LVL 70

Expert Comment

by:Éric Moreau
ID: 23685283
UNION will eliminate the duplicates. If you want to keep all records, you need to use UNION ALL
LVL 32

Accepted Solution

Daniel Wilson earned 1500 total points
ID: 23685287
how's this?

INSERT INTO  SerialItemsAll
Select distinct * from
(SELECT * from SerialItems1
SELECT * from SerialItems2) U

Open in new window

LVL 57

Expert Comment

by:Raja Jegan R
ID: 23685342
To be more accurate as you asked,

INSERT INTO  SerialItemsAll
SELECT * from SerialItems1
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
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

755 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