CODEWITHSUNDEEP
sqlsql-server-2008
kevro
kevro

Reputation: 273

Copy records from one table to another without duplicates

IF object_id('tempdb..#A')  IS NOT NULL DROP TABLE #A
IF object_id('tempdb..#B')  IS NOT NULL DROP TABLE #B
CREATE TABLE #A (fname varchar(20), lname varchar(20))
CREATE TABLE #B (fname varchar(20), lname varchar(20))

INSERT INTO #A
SELECT 'Kevin', 'XP'
UNION ALL
SELECT 'Tammy', 'Win7'
UNION ALL
SELECT 'Wes', 'XP'
UNION ALL 
SELECT 'Susan', 'Win7'
UNION ALL
SELECT 'Kevin', 'Win7'


SELECT * FROM #A

INSERT INTO #B
SELECT a.fname, a.lname FROM #A a
WHERE a.fname NOT IN (SELECT fname from #B)

SELECT * FROM #B

DELETE FROM #B
INSERT INTO #B
SELECT a.fname, a.lname FROM #A a
LEFT OUTER JOIN #B b ON a.fname = b.fname
WHERE a.fname NOT IN (SELECT fname from #B)

SELECT * FROM #B

Both of these examples copy all 5 records to the new table.

I only want to see one unique fname so only one Kevin should show up.

Why don't these work, or is there a better way to do it?

It seems like such a simple thing.

Upvotes: 1

Views: 4493

Answers (4)

JBrooks
JBrooks

Reputation: 10013

This would create rows with unique fname and take Win7 if both Win7 and XP existed. 

INSERT INTO #B
SELECT a.fname, MIN(a.lname) 
FROM #A a
GROUP BY a.fname

Upvotes: 2

Bohemian
Bohemian

Reputation: 424973

Just use DISTINCT over the select query :

INSERT INTO TARGET_TABLE
SELECT DISTINCT * FROM
(
     -- some big query
) x

Upvotes: 0

dmg
dmg

Reputation: 4491

Answering your question, why don't your queries work?

INSERT INTO #B
SELECT a.fname, a.lname FROM #A a
WHERE a.fname NOT IN (SELECT fname from #B)

This operation is evaluated in two different operations. In the first, the SELECT part of the query is executed. It returns a table. At such point #B is empty, hence, every tuple in #A will be part of this result. Then, once this result is computed, this result is inserted into #B. #B will end being a copy of #A.

The DBMS does not insert one tuple, and then re-evaluate the query for the next tuple of #A, as your question seems to imply. Insertions are always done AFTER the query has been completely evaluated.

if your goal is to insert into #B the tuples in #A without duplicates, there are many ways to do that. One of them is:

INSERT INTO #B SELECT distinct * from #A;

--dmg

Upvotes: 1

T I
T I

Reputation: 9933

As per comments, given that W comes before X then you should be able to do

INSERT INTO #B
SELECT fname, lname
FROM (
    SELECT fname, lname,
           ROW_NUMBER() OVER(PARTITION BY fname ORDER BY lname) r
    FROM #A
) t
WHERE r=1

demo

Upvotes: 1

Related Questions