(ODBC driver) timeout expired when query "DELETE FROM mytable"

Hi,

I'm running SQL server 2000. I have at table with about 12 million records.

I want to empty the table. I use the Query option from Tables/mytable/opentable/query.

I type in the query and select Run. The process runs for some time, then raises

an error box as follows: (title)SQL Server Enterprise Manager. The body text says:

[Microsoft][ODBC SQL Server Driver] Timeout Expired. I've tried every timeout

setting I can find. I've tried setting all timeouts to 0 (infinite) to no avail.

Please help.........

Note: I can get the table empty if I select TOP n records, then DELETE FROM but

that takes forever!! It is also not a process that's very amenable to a clean programatic

solution.

Thanks, jack

That's very strange. Setting timeout to '0' should do the trick.

If all you need to do is empty the table you could just call "truncate table table_name". That should run pretty quickly.

Sorry you're having issues. Please reactivate this thread if the 'truncate' command doesn't fix your issue.

~Warren

|||

Warren,

Thanks!

I have changed the query to "Truncate Table" and it's much faster. I haven't

tried it with the large table yet -- I'll have to reload the data before I know for

sure. I used the "Delete" command only because I hadn't stumbled on the "Truncate"

command. I still would like to know why I'm getting the timeout since I'm sure it will

jump up and bite me later because I'm dealing with such large tables, and Murphy is

looking over my shoulder -- ha!

jack

|||Consider that when you execute DELETE, the operation is logged so you are essentially "moving" the deleted rows to the transaction log with all of the associated disk IO expenses. Truncate table is also logged, but simply tells SQL Server to "drop" the data and leave the schema--it's dramatically simpler and faster.|||

William,

"Dramatically" is an understatement! I'm amazed at how quickly the table is

emptied using "Truncate". Although knowing why, it makes perfect sense. Thanks

for the expanation of the 'why', that's even more valuable than the 'how'. Is there an

explicit means of preventing the transaction logging - since it's so time costly? Is

there a downside to such a thing if it exists?

I'm still bewildered about the "timeout expired" error inspired by the length of time

the DELETE takes. I guess I'll have to pull my copy of the "Guide to..." off the shelf

and review ADO/ODBC query timeouts etc.

thanks, jack

|||

No, you can't (and should not) "turn off" the transaction log--it's your safety net. Yes, there are other operations that can be executed without the log getting in the way (like BulkCopy).

Consider that the Delete command must also delete the Index(es) for each row as well as reallocate space and execute other operations that take CPU time, RAM and disk IO. While the Truncate is fast, it also means that the server can clean up the freed space when it has idle time and it needs the space. For long operations you can set the CommandTimeout to a higher number, but whenever I find a neeed to do this I look for a more efficient way to handle the task...

hth

|||

William,

Thanks! I'm in good shape now.

jack

(almost) duplicates

I have a script to remove duplicate records from a table. I have since
found out that I have dups with a date/time stamp that are a few seconds off
(thus, I guess technically making them not duplicates). How can I properly
get rid of the later records. Again the only difference is the seconds in a
date/time field.
Jeff
Please post your table structures and sample data along with the script
which you use to distingush the duplicates. Without clear specifications and
an usable repro, it is hard for others to understand what would
"technically" make some rows non-duplicates when in reality, they are.
Anith
|||Any other column to identitfy the row?
delete t
where exists (select * from t as t1 where t1.col1 = t.col1 and
t1.col_datetime < t.col_datetime)
AMB
"J. Clarke" wrote:

> I have a script to remove duplicate records from a table. I have since
> found out that I have dups with a date/time stamp that are a few seconds off
> (thus, I guess technically making them not duplicates). How can I properly
> get rid of the later records. Again the only difference is the seconds in a
> date/time field.
> Jeff
>
>
|||Hmmm...wouldn't this delete everything except the most recent record (or am
I missreading this)?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...[vbcol=seagreen]
> Any other column to identitfy the row?
> delete t
> where exists (select * from t as t1 where t1.col1 = t.col1 and
> t1.col_datetime < t.col_datetime)
>
> AMB
> "J. Clarke" wrote:
|||I was afraid of this. Basically all the fields in a multiple rows contain
the same values EXCEPT the datetime field. So technically their not
duplicates (the datetimes are a couple of seconds off from each other).
However, I know the front end application had an error that was sticking
them in. I need to keep the 1st record it stuck in and get rid of the rest
that are a few seconds off from the 1st record.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%239EpuQ7MFHA.2656@.TK2MSFTNGP10.phx.gbl...
> Please post your table structures and sample data along with the script
> which you use to distingush the duplicates. Without clear specifications
> and an usable repro, it is hard for others to understand what would
> "technically" make some rows non-duplicates when in reality, they are.
> --
> Anith
>
|||No, it wouldn't . this is a correlated subquery... Take a look at the table
alias t and t1... the same table but treated as 2 different tables...
This takes a row from table t and is trying to decide whether or not to
delete it... It looks to find a row in the same table ( but aliased to t1)
that has the same col1 ( supposedly the id column) but which has a date
which is < than the date of the row in t you are considering for
deletion... If that expression is true, that means there is another row
with the same key but which has an earlier date, so this row must be the
additionaly row that was added later , and there fore should be deleted.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hmmm...wouldn't this delete everything except the most recent record (or
> am I missreading this)?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>
|||On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:

>I have a script to remove duplicate records from a table. I have since
>found out that I have dups with a date/time stamp that are a few seconds off
>(thus, I guess technically making them not duplicates). How can I properly
>get rid of the later records. Again the only difference is the seconds in a
>date/time field.
>Jeff
>
Hi Jeff,
Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
datetime column with a few seconds difference, and that you want to
delete the duplicates if the time difference is no more than 20 seconds,
use:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Col1 = MyTable.Col1
AND b.Col2 = MyTable.Col2
AND b.Col3 = MyTable.Col3
AND b.Col4 < MyTable.Col4
AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||similar to Hugo's idea you could also try the following:
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1,
and my.Col2 = gb.Col2,
and my.Col3 = gb.Col3,
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
This does not take into account any specific time lag. This would find
duplicates that are days apart not just 20 seconds.
This uses a subquery "group by" to identify distinct records and the
"earliest" (min) date stamp and then matches it back and drops all the
matching records that are not the earliest datetime.
The magic comes from the LEFT JOIN and the fact that MinCol4 IS NULL
Good luck.
Message posted via http://www.sqlmonster.com
|||Just to make you feel better, you can try this test to prove the theory:
(also note: I inadvertantly had commas in the "and" section of the left
join)
if exists(select name from sysobjects where name = 'MyTable' and type = 'U')
drop table MyTable
go
create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
insert MyTable select 1, 'Lucy', 101, '1/1/2005'
insert MyTable select 1, 'Lucy', 101, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/1/2005'
insert MyTable select 2, 'Ricky', 102, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/3/2005'
insert MyTable select 3, 'Fred', 103, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/2/2005'
insert MyTable select 4, 'Ethel', 104, '1/3/2005'
insert MyTable select 4, 'Ethel', 104, '1/4/2005'
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1
and my.Col2 = gb.Col2
and my.Col3 = gb.Col3
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
select * from MyTable
Message posted via http://www.sqlmonster.com
|||Ah...Thanks for the explaination Wayne. That helps alot.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%232IH7KGNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> No, it wouldn't . this is a correlated subquery... Take a look at the
> table alias t and t1... the same table but treated as 2 different
> tables...
> This takes a row from table t and is trying to decide whether or not to
> delete it... It looks to find a row in the same table ( but aliased to t1)
> that has the same col1 ( supposedly the id column) but which has a date
> which is < than the date of the row in t you are considering for
> deletion... If that expression is true, that means there is another row
> with the same key but which has an earlier date, so this row must be the
> additionaly row that was added later , and there fore should be deleted.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
> news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>

(almost) duplicates

I have a script to remove duplicate records from a table. I have since
found out that I have dups with a date/time stamp that are a few seconds off
(thus, I guess technically making them not duplicates). How can I properly
get rid of the later records. Again the only difference is the seconds in a
date/time field.
JeffPlease post your table structures and sample data along with the script
which you use to distingush the duplicates. Without clear specifications and
an usable repro, it is hard for others to understand what would
"technically" make some rows non-duplicates when in reality, they are.
Anith|||Any other column to identitfy the row?
delete t
where exists (select * from t as t1 where t1.col1 = t.col1 and
t1.col_datetime < t.col_datetime)
AMB
"J. Clarke" wrote:

> I have a script to remove duplicate records from a table. I have since
> found out that I have dups with a date/time stamp that are a few seconds o
ff
> (thus, I guess technically making them not duplicates). How can I properl
y
> get rid of the later records. Again the only difference is the seconds in
a
> date/time field.
> Jeff
>
>|||Hmmm...wouldn't this delete everything except the most recent record (or am
I missreading this)?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...[vbcol=seagreen]
> Any other column to identitfy the row?
> delete t
> where exists (select * from t as t1 where t1.col1 = t.col1 and
> t1.col_datetime < t.col_datetime)
>
> AMB
> "J. Clarke" wrote:
>|||I was afraid of this. Basically all the fields in a multiple rows contain
the same values EXCEPT the datetime field. So technically their not
duplicates (the datetimes are a couple of seconds off from each other).
However, I know the front end application had an error that was sticking
them in. I need to keep the 1st record it stuck in and get rid of the rest
that are a few seconds off from the 1st record.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%239EpuQ7MFHA.2656@.TK2MSFTNGP10.phx.gbl...
> Please post your table structures and sample data along with the script
> which you use to distingush the duplicates. Without clear specifications
> and an usable repro, it is hard for others to understand what would
> "technically" make some rows non-duplicates when in reality, they are.
> --
> Anith
>|||No, it wouldn't . this is a correlated subquery... Take a look at the table
alias t and t1... the same table but treated as 2 different tables...
This takes a row from table t and is trying to decide whether or not to
delete it... It looks to find a row in the same table ( but aliased to t1)
that has the same col1 ( supposedly the id column) but which has a date
which is < than the date of the row in t you are considering for
deletion... If that expression is true, that means there is another row
with the same key but which has an earlier date, so this row must be the
additionaly row that was added later , and there fore should be deleted.
Hope this helps..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hmmm...wouldn't this delete everything except the most recent record (or
> am I missreading this)?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>|||On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:

>I have a script to remove duplicate records from a table. I have since
>found out that I have dups with a date/time stamp that are a few seconds of
f
>(thus, I guess technically making them not duplicates). How can I properly
>get rid of the later records. Again the only difference is the seconds in
a
>date/time field.
>Jeff
>
Hi Jeff,
Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
datetime column with a few seconds difference, and that you want to
delete the duplicates if the time difference is no more than 20 seconds,
use:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Col1 = MyTable.Col1
AND b.Col2 = MyTable.Col2
AND b.Col3 = MyTable.Col3
AND b.Col4 < MyTable.Col4
AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||similar to Hugo's idea you could also try the following:
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1,
and my.Col2 = gb.Col2,
and my.Col3 = gb.Col3,
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
This does not take into account any specific time lag. This would find
duplicates that are days apart not just 20 seconds.
This uses a subquery "group by" to identify distinct records and the
"earliest" (min) date stamp and then matches it back and drops all the
matching records that are not the earliest datetime.
The magic comes from the LEFT JOIN and the fact that MinCol4 IS NULL
Good luck.
Message posted via http://www.droptable.com|||Just to make you feel better, you can try this test to prove the theory:
(also note: I inadvertantly had commas in the "and" section of the left
join)
if exists(select name from sysobjects where name = 'MyTable' and type = 'U')
drop table MyTable
go
create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
insert MyTable select 1, 'Lucy', 101, '1/1/2005'
insert MyTable select 1, 'Lucy', 101, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/1/2005'
insert MyTable select 2, 'Ricky', 102, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/3/2005'
insert MyTable select 3, 'Fred', 103, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/2/2005'
insert MyTable select 4, 'Ethel', 104, '1/3/2005'
insert MyTable select 4, 'Ethel', 104, '1/4/2005'
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1
and my.Col2 = gb.Col2
and my.Col3 = gb.Col3
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
select * from MyTable
Message posted via http://www.droptable.com|||Ah...Thanks for the explaination Wayne. That helps alot.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%232IH7KGNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> No, it wouldn't . this is a correlated subquery... Take a look at the
> table alias t and t1... the same table but treated as 2 different
> tables...
> This takes a row from table t and is trying to decide whether or not to
> delete it... It looks to find a row in the same table ( but aliased to t1)
> that has the same col1 ( supposedly the id column) but which has a date
> which is < than the date of the row in t you are considering for
> deletion... If that expression is true, that means there is another row
> with the same key but which has an earlier date, so this row must be the
> additionaly row that was added later , and there fore should be deleted.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
> news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>

(almost) duplicates

I have a script to remove duplicate records from a table. I have since
found out that I have dups with a date/time stamp that are a few seconds off
(thus, I guess technically making them not duplicates). How can I properly
get rid of the later records. Again the only difference is the seconds in a
date/time field.
JeffPlease post your table structures and sample data along with the script
which you use to distingush the duplicates. Without clear specifications and
an usable repro, it is hard for others to understand what would
"technically" make some rows non-duplicates when in reality, they are.
--
Anith|||Any other column to identitfy the row?
delete t
where exists (select * from t as t1 where t1.col1 = t.col1 and
t1.col_datetime < t.col_datetime)
AMB
"J. Clarke" wrote:
> I have a script to remove duplicate records from a table. I have since
> found out that I have dups with a date/time stamp that are a few seconds off
> (thus, I guess technically making them not duplicates). How can I properly
> get rid of the later records. Again the only difference is the seconds in a
> date/time field.
> Jeff
>
>|||Hmmm...wouldn't this delete everything except the most recent record (or am
I missreading this)?
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
> Any other column to identitfy the row?
> delete t
> where exists (select * from t as t1 where t1.col1 = t.col1 and
> t1.col_datetime < t.col_datetime)
>
> AMB
> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few seconds
>> off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>|||I was afraid of this. Basically all the fields in a multiple rows contain
the same values EXCEPT the datetime field. So technically their not
duplicates (the datetimes are a couple of seconds off from each other).
However, I know the front end application had an error that was sticking
them in. I need to keep the 1st record it stuck in and get rid of the rest
that are a few seconds off from the 1st record.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%239EpuQ7MFHA.2656@.TK2MSFTNGP10.phx.gbl...
> Please post your table structures and sample data along with the script
> which you use to distingush the duplicates. Without clear specifications
> and an usable repro, it is hard for others to understand what would
> "technically" make some rows non-duplicates when in reality, they are.
> --
> Anith
>|||No, it wouldn't . this is a correlated subquery... Take a look at the table
alias t and t1... the same table but treated as 2 different tables...
This takes a row from table t and is trying to decide whether or not to
delete it... It looks to find a row in the same table ( but aliased to t1)
that has the same col1 ( supposedly the id column) but which has a date
which is < than the date of the row in t you are considering for
deletion... If that expression is true, that means there is another row
with the same key but which has an earlier date, so this row must be the
additionaly row that was added later , and there fore should be deleted.
Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hmmm...wouldn't this delete everything except the most recent record (or
> am I missreading this)?
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>> Any other column to identitfy the row?
>> delete t
>> where exists (select * from t as t1 where t1.col1 = t.col1 and
>> t1.col_datetime < t.col_datetime)
>>
>> AMB
>> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few seconds
>> off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>
>|||On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:
>I have a script to remove duplicate records from a table. I have since
>found out that I have dups with a date/time stamp that are a few seconds off
>(thus, I guess technically making them not duplicates). How can I properly
>get rid of the later records. Again the only difference is the seconds in a
>date/time field.
>Jeff
>
Hi Jeff,
Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
datetime column with a few seconds difference, and that you want to
delete the duplicates if the time difference is no more than 20 seconds,
use:
DELETE FROM MyTable
WHERE EXISTS
(SELECT *
FROM MyTable AS b
WHERE b.Col1 = MyTable.Col1
AND b.Col2 = MyTable.Col2
AND b.Col3 = MyTable.Col3
AND b.Col4 < MyTable.Col4
AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||similar to Hugo's idea you could also try the following:
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1,
and my.Col2 = gb.Col2,
and my.Col3 = gb.Col3,
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
This does not take into account any specific time lag. This would find
duplicates that are days apart not just 20 seconds.
This uses a subquery "group by" to identify distinct records and the
"earliest" (min) date stamp and then matches it back and drops all the
matching records that are not the earliest datetime.
The magic comes from the LEFT JOIN and the fact that MinCol4 IS NULL
Good luck.
--
Message posted via http://www.sqlmonster.com|||Just to make you feel better, you can try this test to prove the theory:
(also note: I inadvertantly had commas in the "and" section of the left
join)
if exists(select name from sysobjects where name = 'MyTable' and type = 'U')
drop table MyTable
go
create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
insert MyTable select 1, 'Lucy', 101, '1/1/2005'
insert MyTable select 1, 'Lucy', 101, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/1/2005'
insert MyTable select 2, 'Ricky', 102, '1/2/2005'
insert MyTable select 2, 'Ricky', 102, '1/3/2005'
insert MyTable select 3, 'Fred', 103, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/1/2005'
insert MyTable select 4, 'Ethel', 104, '1/2/2005'
insert MyTable select 4, 'Ethel', 104, '1/3/2005'
insert MyTable select 4, 'Ethel', 104, '1/4/2005'
DELETE MyTable
from MyTable my
LEFT JOIN (
select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
group by Col1, Col2, Col3) as gb
ON my.Col1 = gb.Col1
and my.Col2 = gb.Col2
and my.Col3 = gb.Col3
and my.Col4 = gb.MinCol4
where gb.MinCol4 IS NULL
select * from MyTable
--
Message posted via http://www.sqlmonster.com|||Ah...Thanks for the explaination Wayne. That helps alot.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%232IH7KGNFHA.1476@.TK2MSFTNGP09.phx.gbl...
> No, it wouldn't . this is a correlated subquery... Take a look at the
> table alias t and t1... the same table but treated as 2 different
> tables...
> This takes a row from table t and is trying to decide whether or not to
> delete it... It looks to find a row in the same table ( but aliased to t1)
> that has the same col1 ( supposedly the id column) but which has a date
> which is < than the date of the row in t you are considering for
> deletion... If that expression is true, that means there is another row
> with the same key but which has an earlier date, so this row must be the
> additionaly row that was added later , and there fore should be deleted.
> Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "J. Clarke" <jaclarke01@.hotmail.comNOSPAM> wrote in message
> news:u8lQjW$MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>> Hmmm...wouldn't this delete everything except the most recent record (or
>> am I missreading this)?
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:E562DCB5-4F62-4DD2-B859-2F357856DBF4@.microsoft.com...
>> Any other column to identitfy the row?
>> delete t
>> where exists (select * from t as t1 where t1.col1 = t.col1 and
>> t1.col_datetime < t.col_datetime)
>>
>> AMB
>> "J. Clarke" wrote:
>> I have a script to remove duplicate records from a table. I have since
>> found out that I have dups with a date/time stamp that are a few
>> seconds off
>> (thus, I guess technically making them not duplicates). How can I
>> properly
>> get rid of the later records. Again the only difference is the seconds
>> in a
>> date/time field.
>> Jeff
>>
>>
>|||Thanks Hugo - I'm not sure if this will work for me as the time may expand
beyond a set periord of seconds (maybe to a few minutes?). I get the gist
though - I appreciate your help and explaination!
Jeff
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:92nj411t3o257ghsm8jfm3oicddhrc6p3k@.4ax.com...
> On Mon, 28 Mar 2005 10:35:43 -0500, J. Clarke wrote:
>>I have a script to remove duplicate records from a table. I have since
>>found out that I have dups with a date/time stamp that are a few seconds
>>off
>>(thus, I guess technically making them not duplicates). How can I
>>properly
>>get rid of the later records. Again the only difference is the seconds in
>>a
>>date/time field.
>>Jeff
> Hi Jeff,
> Assuming Col1, Col2 and Col3 are exactly the same and Col4 is the
> datetime column with a few seconds difference, and that you want to
> delete the duplicates if the time difference is no more than 20 seconds,
> use:
> DELETE FROM MyTable
> WHERE EXISTS
> (SELECT *
> FROM MyTable AS b
> WHERE b.Col1 = MyTable.Col1
> AND b.Col2 = MyTable.Col2
> AND b.Col3 = MyTable.Col3
> AND b.Col4 < MyTable.Col4
> AND b.Col4 >= DATEDIFF(second, 20, MyTable.Col4))
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Wow! Thanks for the explaination and the example. Your example I think may
work for the best of me. From one example I was peeking at I may up to 4000
'dups' off the 1st record (yikes!). I'm concerned tho, that I may have an
actual record on another day and I need to ensure I'm not including those
puppies
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b532bdf904ce437fb849575d7ccfd8fb@.SQLMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
> if exists(select name from sysobjects where name = 'MyTable' and type => 'U')
> drop table MyTable
> go
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
> select * from MyTable
> --
> Message posted via http://www.sqlmonster.com|||I don't think this is gonna work Geoffrey - I have 928,457 records in the DB
and the scripts been running now for 16+ hours - I don't think my sites are
gonna want to be shutdown this long. It's on my local box, nothing other
than SQL QA is connected to it.
Here is my complete script (maybe I did it wrong):
DELETE dbo.TRM_VISN_REPORT
from dbo.TRM_VISN_REPORT my
LEFT JOIN (
select VistaUserName, MIN(DateTimeofCall)as MinDateTimeofCall,
UserLocStation, CallDuration, CPTCode, CPTDescription, ClinicalCall,
RegisteredPatient, ChiefComplaint, FollowupIntRec,
FollowupIntAct, FollowupLoc, CallerResponse, CallerArea, VEJDIFN,
TypeOfCall, CallFiledAtStation, PatientName, SSN
from dbo.TRM_VISN_REPORT
group by VistaUserName, UserLocStation, CallDuration, CPTCode,
CPTDescription, ClinicalCall, RegisteredPatient, ChiefComplaint,
FollowupIntRec,
FollowupIntAct, FollowupLoc, CallerResponse, CallerArea, VEJDIFN,
TypeOfCall, CallFiledAtStation, PatientName, SSN) as gb
ON my.VistaUserName = gb.VistaUserName
and my.DateTimeofCall = gb.MinDateTimeofCall
and my.UserLocStation = gb.UserLocStation
and my.CallDuration = gb.CallDuration
and my.CPTCode = gb.CPTCode
and my.CPTDescription = gb.CPTDescription
and my.ClinicalCall = gb.ClinicalCall
and my.RegisteredPatient = gb.RegisteredPatient
and my.ChiefComplaint = gb.ChiefComplaint
and my.FollowupIntRec = gb.FollowupIntRec
and my.FollowupIntAct = gb.FollowupIntAct
and my.FollowupLoc = gb.FollowupLoc
and my.CallerResponse = gb.CallerResponse
and my.CallerArea = gb.CallerArea
and my.VEJDIFN = gb.VEJDIFN
and my.TypeOfCall = gb.TypeOfCall
and my.CallFiledAtStation = gb.CallFiledAtStation
and my.PatientName = gb.PatientName
and my.SSN = gb.SSN
where gb.MinDateTimeofCall IS NULL
Jeff
"Geoffrey Kahan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b532bdf904ce437fb849575d7ccfd8fb@.SQLMonster.com...
> Just to make you feel better, you can try this test to prove the theory:
> (also note: I inadvertantly had commas in the "and" section of the left
> join)
> if exists(select name from sysobjects where name = 'MyTable' and type ='U')
> drop table MyTable
> go
> create table MyTable(Col1 int, Col2 varchar(10), Col3 int, Col4 datetime)
> insert MyTable select 1, 'Lucy', 101, '1/1/2005'
> insert MyTable select 1, 'Lucy', 101, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/1/2005'
> insert MyTable select 2, 'Ricky', 102, '1/2/2005'
> insert MyTable select 2, 'Ricky', 102, '1/3/2005'
> insert MyTable select 3, 'Fred', 103, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/1/2005'
> insert MyTable select 4, 'Ethel', 104, '1/2/2005'
> insert MyTable select 4, 'Ethel', 104, '1/3/2005'
> insert MyTable select 4, 'Ethel', 104, '1/4/2005'
> DELETE MyTable
> from MyTable my
> LEFT JOIN (
> select Col1, Col2, Col3, MIN(Col4)as MinCol4 from MyTable
> group by Col1, Col2, Col3) as gb
> ON my.Col1 = gb.Col1
> and my.Col2 = gb.Col2
> and my.Col3 = gb.Col3
> and my.Col4 = gb.MinCol4
> where gb.MinCol4 IS NULL
> select * from MyTable
> --
> Message posted via http://www.sqlmonster.comsql

"NOT IN" SQL Statement

Hi,

I need to create a SQL statement as a datasource. I need the SQL statement to exclude the records that exists in another table!

I tried to use the where clause

SELECT X.A,X.B,X.C,X.D From X

where X.B,X.C,X.D NOT IN (

SELECT Y.E,Y.F,Y.G

From Y

)

The SQL Statement works fine when I have only one column in the NOT IN part but gives me syntax error when I have more than one column in the NOT IN part.

Please advise how can I write this SQL statement in T-SQL

Thanks,

Aref

select a, b, c, d, e, f

from table1 as t1

where not exists(select * from table2 as t2 where t2.a = t1.a and t2.b = t1.b and t2.c = t1.c)

|||For more information about the provided syntax by joeydj look for the keyword correlated subqueries.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

"batch size setting" ?

Is there a "bacth size setting" or some such t-sql option that determines the number of records sql will hold before commiting them as a group to the database?

TIA,

Barkingdog

Not that I know of, but you can use marshalling by setting up a loop and tellng it to commit.

'Space Available' question

We are upgrading tables, stored procedures and views in a sql server database. One of the enhancements involves clearing almost all of the records in the database. The database contains healthcare claim data. The Deletes removed the claims and cleared tables with aggregated data.

Prior to the upgrade teh database reported a 'Size' of 52 gigs with about 14 gigs of 'Space Available'.

Post upgrade the database reports a 'Size' of 48 gigs with about 38 gigs of 'Space Available'.

We're hoping to get the database to 'give back' some of the 38 gigs of space it is holding. In order to accomplish this we have tried:
1) the maintence utility that 'gets back' space -- 1 or 2 gigs impact.
2) reindex and run space utility -- no impact.

Other Constraints. It is a production database for which we do not have an exact replica in test. It would be a significant effort to move the db to a secure test environment. Translation: We have to be careful what we 'try' in terms of fixes. We can certainly go to a backup but it is not a test environment.

And advice or suggestions that folks might have in terms of how to manage 'Space Available' are appreciated.

Regards

rayko

Hi rayko,

You did not indicate if you had autogrow enabled on this database.

If that's the case, and you don't have the counter-part 'shrink' then this is the behavior you should expect.

Can you clarify "give back" in case I have mis-interpreted what you meant?

Thanks,

Terrence Nevins

SQL Server Program Manager

|||

hi,

please try to run dbcc shrinkdatabase

and dbcc shrinkfile to specifically shrink the files that has a lot

of free space and to specifically shrink the transactio logs

for more info please see BOL

thanks,

joey

#DELETED#

I know this is poor practice but I wanted to append 3 records of data to a SQL table. I have a link to that table in MS Access 2000. I open up the table in Access , scroll to the end, and paste the 3 records into the table. Immediatly the results of that
process are 3 lines of #DELETED# until I refrexh the table ( close then re-open). My question is why does it do this as I expect to see the new record immediatly after I have pasted them?
Any help/ explanation would be greatly appreciated
Does the table have a primary key?
Rand
This posting is provided "as is" with no warranties and confers no rights.

#DELETED#

I know this is poor practice but I wanted to append 3 records of data to a S
QL table. I have a link to that table in MS Access 2000. I open up the tabl
e in Access , scroll to the end, and paste the 3 records into the table. Imm
ediatly the results of that
process are 3 lines of #DELETED# until I refrexh the table ( close then re-o
pen). My question is why does it do this as I expect to see the new record
immediatly after I have pasted them?
Any help/ explanation would be greatly appreciatedDoes the table have a primary key?
Rand
This posting is provided "as is" with no warranties and confers no rights.

#DELETED#

I know this is poor practice but I wanted to append 3 records of data to a SQL table. I have a link to that table in MS Access 2000. I open up the table in Access , scroll to the end, and paste the 3 records into the table. Immediatly the results of that process are 3 lines of #DELETED# until I refrexh the table ( close then re-open). My question is why does it do this as I expect to see the new record immediatly after I have pasted them?
Any help/ explanation would be greatly appreciatedDoes the table have a primary key?
Rand
This posting is provided "as is" with no warranties and confers no rights.