Deleting million rows after checking duplication

I have a table(9 fields) which contains around 10 million records. Within these 10 million records, don't know how many are duplicated rows. I wrote a cursor which checks duplication on all of the 9 fields within each row, and then returns back with the count of rows that are duplicated. I subtact 1 row and delete all the other rows. Problem is that this thing takes a lot of time to execute. At the current rate(approx. 90 records/hour) this cursor is going to take months to clean the table.

There is no PK or no indexing what so ever on the table, and I HAVE to check each and every field for duplication(all except 1 fields are nvarchar).

Please help me. I need to sort this out

What about doing a grouping on the table and pulling hte data out to another table.

Something like:

Select cola,ColB,Colc (all columns here)
INTO SomeNewTable
FROM SomeTable
Group by cola,ColB,Colc (all columns here)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
Ryan
Create an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>
|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>
sql

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hi All,
>> If I delete a field in SQL Server, will all constraints, foreign keys,
>> indices etc. associated with that field also be removed?
>> Thanks in advance
>> Ryan
>>
>

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>

Deleteing specific rows from a table which have similar values

I have the following table and I'd like to delete rows which have the same
values in the fields MergeFromURN and MergeToURN
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
3 100 300
4 700 800
5 700 800
After my query I'd like my table to look like the following: -
RecNo MergeFromURN MergeToURN
1 500 600
2 100 300
4 700 800
I've no idea how to carry out this delete query as whenever I try I also
seem to delete both rows which are the same. Can someone help me to do this.
Thanks for any help anyone can give me.Hi Stephan,
this should work. Just replace 'myTable' with your table name.
Depending on the amount of data, you might want to create some indexes on
the temp-table.
Micha.
SELECT tab1.RecNo, tab1.MergeFromURN, tab1.MergeToURN
INTO #temp
FROM myTable tab1
JOIN myTable tab2 ON (tab1.RecNo <> tab2.RecNo AND tab1.MergeFromURN =
tab2.MergeFromUrn AND tab1.MergeToUrn = tab2.MergeToUrn)
DELETE
FROM myTable
WHERE RecNo IN (SELECT RecNo
FROM #temp)
AND RecNo NOT IN (SELECT MIN(RecNo)
FROM #temp
GROUP BY MergeFromURN, MergeToURN)
DROP TABLE #temp
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:76146231-179D-497C-ADA3-AE707F279984@.microsoft.com...
>I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do
> this.
> Thanks for any help anyone can give me.|||Hi Stephen,
May this statement solve your Problem
CREATE TABLE TABLENAME(RecNo INT, MergeFromURN INT ,MergeToURN
INT)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 1,500, 600)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 2,100, 300)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 3,100, 300 )
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 4,700, 800)
INSERT INTO TABLENAME(RecNo , MergeFromURN ,MergeToURN ) VALUES
( 5,700, 800)
SELECT * FROM TABLENAME
DELETE FROM TABLENAME WHERE RECNO NOT IN (SELECT MIN(RECNO) FROM
TABLENAME GROUP BY MergeFromURN,MergeToURN)
SELECT * FROM TABLENAME
DROP TABLE TABLENAME
If this statement does solve your purpose let me know.
Please post DDL,DML statements so that others can test their queries.
With warm regards
Jatinder Singh
Stephen wrote:
> I have the following table and I'd like to delete rows which have the same
> values in the fields MergeFromURN and MergeToURN
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 3 100 300
> 4 700 800
> 5 700 800
> After my query I'd like my table to look like the following: -
> RecNo MergeFromURN MergeToURN
> 1 500 600
> 2 100 300
> 4 700 800
> I've no idea how to carry out this delete query as whenever I try I also
> seem to delete both rows which are the same. Can someone help me to do thi
s.
> Thanks for any help anyone can give me.

DELETE without transaction?

Hi,
I have a database with about 50 GB - most of the data in one single table
with only three fields. I need to clean up this table frequently (executing
DELETE stantements). My problem is that this locks the table so that BULK
INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
and more) while deleting the data.
Is there a way to delete without using the transaction log and while doing a
bulk insert?
CU,
SvenHi,
I think TRUNCATE TABLE is what you are looking for.
I'm not sure you can do anything else while truncating, but
truncate is very fast.
Check BOL for further information.
Ciao,
Cosmin.
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a database with about 50 GB - most of the data in one single table
> with only three fields. I need to clean up this table frequently
(executing
> DELETE stantements). My problem is that this locks the table so that BULK
> INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
> and more) while deleting the data.
> Is there a way to delete without using the transaction log and while doing
a
> bulk insert?
> CU,
> Sven
>|||truncate table is a good way of cleaning out the entire
table, assuming there are no foreign key constraints
for deleting large number of rows, try doing it in small
batches, say 1000-10,000 rows at a time, adjust the row
count to keep the batch run time sufficiently short
>--Original Message--
>Hi,
>I have a database with about 50 GB - most of the data in
one single table
>with only three fields. I need to clean up this table
frequently (executing
>DELETE stantements). My problem is that this locks the
table so that BULK
>INSERT jobs cannot execute. Also the transaction lok
fills up a lot (15 GB
>and more) while deleting the data.
>Is there a way to delete without using the transaction
log and while doing a
>bulk insert?
>CU,
>Sven
>
>.
>|||The problem is that I need a where statement - this is not supported by
TRUNCATE TABLE (that's one reason, why it's so fast).
"Cosmin" <cosmin.onea@.infoworld.ro> wrote in message
news:O1NbTJyYDHA.2476@.tk2msftngp13.phx.gbl...
> Hi,
> I think TRUNCATE TABLE is what you are looking for.
> I'm not sure you can do anything else while truncating, but
> truncate is very fast.
> Check BOL for further information.
> Ciao,
> Cosmin.
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I have a database with about 50 GB - most of the data in one single
table
> > with only three fields. I need to clean up this table frequently
> (executing
> > DELETE stantements). My problem is that this locks the table so that
BULK
> > INSERT jobs cannot execute. Also the transaction lok fills up a lot (15
GB
> > and more) while deleting the data.
> >
> > Is there a way to delete without using the transaction log and while
doing
> a
> > bulk insert?
> >
> > CU,
> > Sven
> >
> >
>

Delete where 2 fields do not match

I need to be able to delete records from 1 table that do not match 2 fields
on a 2nd table. I was trying to get the SELECT to work first to find the
records that are not full matches. Below is my code and would appreciate
any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
was not sure. Thanks.
SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
FROM dbo.ClientWorkerStatus, ClientWorkerLink
WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
The problem with this is it gives me every record that does not match on
WorkerID. What I want is only those records that are on the
ClientWorkerStatus table that do not have exact match between ClientID and
WorkerID.David,
Can you post the DDL, sample data (INSERTs) and the desired resultset?
HTH
Jerry
"David" <dlchase@.lifetimeinc.com> wrote in message
news:OYqCKc2yFHA.3180@.TK2MSFTNGP14.phx.gbl...
>I need to be able to delete records from 1 table that do not match 2 fields
>on a 2nd table. I was trying to get the SELECT to work first to find the
>records that are not full matches. Below is my code and would appreciate
>any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
>was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>|||Try this,
SELECT ClientID, WorkerID
FROM ClientWorkerStatus C
WHERE (ClientID IN (SELECT ClientID
FROM ClientWorkerLink
WHERE (ClientID = C.ClientID) AND (WorkerID <>
C.WorkerID)))
"David" wrote:

> I need to be able to delete records from 1 table that do not match 2 field
s
> on a 2nd table. I was trying to get the SELECT to work first to find the
> records that are not full matches. Below is my code and would appreciate
> any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
> was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>
>|||Try,
delete dbo.ClientWorkerStatus
where not exists (
select *
from dbo.ClientWorkerLink as a
where a.ClientID = dbo.ClientWorkerStatus.ClientID
and a.WorkerID = dbo.ClientWorkerStatus.WorkerID
)
AMB
"David" wrote:

> I need to be able to delete records from 1 table that do not match 2 field
s
> on a 2nd table. I was trying to get the SELECT to work first to find the
> records that are not full matches. Below is my code and would appreciate
> any help. I thought of maybe a NOT IN() or NOT EXISTS() kind of code, but
> was not sure. Thanks.
> SELECT dbo.ClientWorkerStatus.ClientID, dbo.ClientWorkerStatus.WorkerID
> FROM dbo.ClientWorkerStatus, ClientWorkerLink
> WHERE (dbo.ClientWorkerStatus.ClientID = dbo.ClientWorkerLink.ClientID AND
> dbo.ClientWorkerStatus.WorkerID <> dbo.ClientWorkerLink.WorkerID)
> The problem with this is it gives me every record that does not match on
> WorkerID. What I want is only those records that are on the
> ClientWorkerStatus table that do not have exact match between ClientID and
> WorkerID.
>
>|||Got it! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***

DELETE rows involving auto-increment fields

I tried to delete a couple of records from a table which has a ID field
which is auto-incremented(IDENTITY) . But, after deletion... when I
tried to insert new records, the ID field gets incremented from
previous next value (before deletion). Is there any way to get around
this? Plz helpLookup DBCC CHECKIDENT
example:
Force the current identity value to 30
This example forces the current identity value in the jobs table to a
value of 30.
USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
http://sqlservercode.blogspot.com/|||"juventus" <saurabh.kotkar@.gmail.com> wrote in message
news:1140806282.998562.62590@.j33g2000cwa.googlegroups.com...
>I tried to delete a couple of records from a table which has a ID field
> which is auto-incremented(IDENTITY) . But, after deletion... when I
> tried to insert new records, the ID field gets incremented from
> previous next value (before deletion). Is there any way to get around
> this? Plz help
>
First of all, a lot of things can happen that may cause gaps in Identity.
You have found one. Rolled back transactions are another.
SQL only guarantees that the values will be incremental.
See DBCC Checkident in Books On-Line.|||Thanks a lot man..! It worked. I was able to reseed the increment
value..|||Confirm that you have a unique index or constraint on this column. I think
it may be possible for SQL Server to repeat sequences of identity numbers if
the maximum value for data type is reached or as a result of reseeding to a
value less than the current highest value.
"juventus" <saurabh.kotkar@.gmail.com> wrote in message
news:1140808374.186842.221090@.u72g2000cwu.googlegroups.com...
> Thanks a lot man..! It worked. I was able to reseed the increment
> value..
>

Delete row from MS SQL Server 2000

Hi, I'm using SQL Server 2000 Personal Edition. I have created a table 'Student' which has 2 fields.

ID -- Varchar(10), Primary Key - contains ID of a student

NAME -- Varchar(50) -- contains names of student

ID NAME

0107200701 abcd

0107200702 cdgdh

0107200703 iyiylklk

.

.

I want to delete a complete row (all entries) from the "student" table using/specifing 'ID'. What will be the SQL query for that?

Hi

Try to use this (and please read help )

delete from Student where [ID] = '0107200701'

You should change the ID to your real ID

Regards,

Janos