1

I have a temp table called T_TEMP where I have 3 fields (varchar) as follow:

id, number, name.

I have another table called T where I have 3 fields, one with identity (it is Sql Server 2008) called id, another called number and name. Both are varchars and id is an int.

I tried the following statement:

Insert into T (number,name) select distinct number,name from T_TEMP

Despite this the insert statement has inserted all the rows, even the duplicated rows. Specifically there are 42 rows with duplicated data.

Can anyone help me with some statement to do the insert or delete the duplicate in the temp table?

EDIT

Data example:

ID        |         NUMBER       |   NAME

----------| -------------------- | ---------

25613278  | XX111111111BB        | B2930

25613279  | XX111111111BB        | G6336

25613280  | XX111111111BB        | G2344
Improve this question
2
  • I've updated my answer Commented Nov 22, 2016 at 14:18
  • I've updated again. Now it's OK Commented Nov 22, 2016 at 15:52

4 Answers 4

Reset to default
1

Finally I have the solution:

Statement

INSERT INTO T
      (number,name) SELECT number,name
  FROM (
                SELECT  number,name,
                        ROW_NUMBER() OVER(PARTITION BY number ORDER BY id DESC) rn
                    FROM T_TEMP
) a
WHERE rn = 1

Explanation

This statement ensures to take only rows where RN, which is a self computed field is equal to 1. With this where clause it ensures to avoid duplicates. The RN field is computed by number wich is the field that I want to be unique. The order by id Desc clause allows to take the min id value as the unique number field.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi, but if you run twice this statement, you have duplicated rows. Try
Yes it is true, but the problem was to avoid the duplicated in the first insert. Theorically I won't insert again from temp table to final table. It is because is a manual data cleaning process.
0

One option is to add a unique index/constraint on the number and name columns:

CREATE UNIQUE INDEX u_index ON T (number, name)

Now if your INSERT statement would include records which are duplicate with respect to number, name combination they would be rejected at the database level.

Improve this answer

Comments

0

INSERT INTO T (NUMBER, NAME) SELECT NUMBER, NAME FROM T_TEMP GROUP BY NUMBER, NAME

Improve this answer

Comments

0

You need two NOT EXISTS clauses. The first on the table T_TEMP (so you get only the first row for the same number). The second, because if you run many times this query you guarantee none duplicate is present in the main table (T).

Try this:

 INSERT INTO T (number, name)
  SELECT tt.number, tt.name
  FROM T_TEMP tt
  WHERE NOT EXISTS(SELECT 'PREVIOUS'
  FROM T_TEMP tt2
  WHERE tt2.number = tt.number
  AND tt2.name < tt.name)
  AND NOT EXISTS(SELECT 'DUPLICATE'
  FROM T t2
  WHERE t2.number = tt.number)
Improve this answer

4 Comments

I have tried it and It has duplicated rows, but I read carefully your answer and I see that it guarantee that doesn't exist rows with couple values, but what I need is that numbers is not duplicated. For example I have this: NUMBER:1 NAME:yuio // NUMBER:1 NAME:peri. I only want to insert one of them.
I tried it and it doesn't work. The select count(*) of both tables is the same :S
@Maik: Please post a set of your input data
I have the solution. I just posted it. If you want you can check it. Thanks for your help. How do you give the style to the data table?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.