Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

remove rows from staging table that are duplicated before insert the data into the database normal tables Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 11:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 757, Visits: 1,079
Hi,

I have this table:

CREATE TABLE [dbo].[BULK_ACTIVIDADES](
[CODCTB] [bigint] NULL,
[NIF_ANTIGO] [varchar](20) NOT NULL,
[NIF] [varchar](20) NULL,
[FILIAL_NUMBER] [varchar](20) NULL,
[INDEX_POS] [int] NULL,
[DESC_ACTIVIDADE] [varchar](100) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


I want to delete the duplicated rows.

I could do something like:


select * into bulk_actividades1
from
(select * from bulk_actividades
union
select * from bulk_actividades) r

truncate table bulk_actividades
drop table bulk_actividades

select * into bulk_actividades from bulk_actvidades1

truncate bulk_actividades1
drop table bulk_actvidades1


But this table and other have a lot of rows (3 million) so I ask. Do you SQL Server experts know a faster way of doing this in t-sql?

thank you.
Post #1566804
Posted Thursday, May 1, 2014 11:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 10,342, Visits: 13,352
One thing you could do is just use a SELECT DISTINCT when transferring the data, but if you want to remove duplicates I'd suggest this:

WITH    deDupe
AS (
SELECT
BA.CODCTB,
BA.NIF_ANTIGO,
BA.NIF,
BA.FILIAL_NUMBER,
BA.INDEX_POS,
BA.DESC_ACTIVIDADE,
ROW_NUMBER() OVER (PARTITION BY BA.CODCTB, BA.NIF_ANTIGO,
BA.NIF, BA.FILIAL_NUMBER, BA.INDEX_POS,
BA.DESC_ACTIVIDADE ORDER BY BA.CODCTB) AS RowNo
FROM
dbo.BULK_ACTIVIDADES AS BA
)
DELETE FROM
deDupe
WHERE
deDupe.RowNo > 1





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566808
Posted Thursday, May 1, 2014 11:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 757, Visits: 1,079
Is that faster than my exemple?
Post #1566810
Posted Thursday, May 1, 2014 11:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 10,342, Visits: 13,352
I believe the pattern I have proposed will outperform the pattern you have proposed. My proposal only makes one pass at the data while yours makes multiple passes and multiple copies so much more write activity in particular.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1566813
Posted Thursday, May 1, 2014 12:54 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 757, Visits: 1,079
understood. I will test it.

Thank you.
Post #1566830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse