I need to to modify a stored procedure so that it can
determine if it is already running. I could create a
table to add/opdate a status record, but I would prefer
to read a system table looking for the proc running on
a connection if possible.
If anyone has done something like this, I would really
like to hear about it.
tia,
BillYou can't do that directly, but you can use an application lock inside your
stored procedure. See sp_getapplock in Books Online for the details.
Jacco Schalkwijk
SQL Server MVP
<bill_sheets@.hotmail.com> wrote in message
news:1107442939.517642.53580@.g14g2000cwa.googlegroups.com...
>I need to to modify a stored procedure so that it can
> determine if it is already running. I could create a
> table to add/opdate a status record, but I would prefer
> to read a system table looking for the proc running on
> a connection if possible.
> If anyone has done something like this, I would really
> like to hear about it.
> tia,
> Bill
>
Showing posts with label record. Show all posts
Showing posts with label record. Show all posts
Friday, March 23, 2012
How to determine if a record is not sync''d
Hello.
I'm not sure if this is possible, but I was hoping to get an answer
here. I'm creating a Windows Mobile 5.0 Smartphone program which
uses SqlCeResultSets as its data objects.
I was wondering if there is
anyway to easily tell if there are records which have been inserted
into the local DB but have not been sync'd up to the publisher yet.
I use merge replication to sync the device with the host and I know
that the merge replicator obviously can tell the difference. Is there
any special field or flag that I can check to see if the record is
only local, on the subscriber side?
Thanks!
SMPfrom msmerge_genhistory you can find out the genstatus of each generation. For each generation that has not been delivered you can then use msmerge_contents to lookup the generation changes and that will give you the rowguid of the record that has not been delivered and the tablenick.
Take a look at this page:
http://www.replicationanswers.com/MergeInternals1.asp
Martin
How to determine if a record is not sync''d
Hello.
I'm not sure if this is possible, but I was hoping to get an answer
here. I'm creating a Windows Mobile 5.0 Smartphone program which
uses SqlCeResultSets as its data objects.
I was wondering if there is
anyway to easily tell if there are records which have been inserted
into the local DB but have not been sync'd up to the publisher yet.
I use merge replication to sync the device with the host and I know
that the merge replicator obviously can tell the difference. Is there
any special field or flag that I can check to see if the record is
only local, on the subscriber side?
Thanks!
SMPfrom msmerge_genhistory you can find out the genstatus of each generation. For each generation that has not been delivered you can then use msmerge_contents to lookup the generation changes and that will give you the rowguid of the record that has not been delivered and the tablenick.
Take a look at this page:
http://www.replicationanswers.com/MergeInternals1.asp
Martin
Friday, March 9, 2012
how to delete?..
what codes should i use if i want to delete a record in sql using vb6?.,
because this doesnt work..
Confirm = MsgBox ("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adodc1.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If
-grrr.,they are teaching us about sql now??.,
gggrrrrrr!!!!.,our skuL is useless!.,connectionObj.execute ("Delete from tablename where keyColumn=" & keyVal)|||thankyou.,:)
because this doesnt work..
Confirm = MsgBox ("Are you sure you want to delete this record?", vbYesNo, "Deletion Confirmation")
If Confirm = vbYes Then
adodc1.Recordset.Delete
MsgBox "Record Deleted!", , "Message"
Else
MsgBox "Record Not Deleted!", , "Message"
End If
-grrr.,they are teaching us about sql now??.,
gggrrrrrr!!!!.,our skuL is useless!.,connectionObj.execute ("Delete from tablename where keyColumn=" & keyVal)|||thankyou.,:)
Wednesday, March 7, 2012
How to delete row 169441?
MSSQL database table DATE field has corruption. The error message says it is at row 169441. I cannot finish an export, browse to the record or delete it. I got the bad record below by exporting to a text file and this was the last record before it stopped.
This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"Can you select that row with this where clause?
where date > '1/1/2050'|||No. where date > '1/1/2050' returns 'invalid date data'|||If there is no "key" on this table you could try:
Backup your database
Set RowCount 169440 -- Process only first nnnn rows.
Select * Into #TMP_Table from YourTable
Set RowCount 169441 -- Process only first nnnn rows.
Delete From YourTable -- Should delete all rows copied plus the bad one.
Set RowCount 0 -- Process all rows
Insert Into @.TMP_Table Select * From YourTable -- Copy the rest
This is a big "??". The "Select"s and "Delete"s may not process all rows in the same order, but they probably will. If nothing else works it is worth a try. Just make sure that you can restore from your backup!
OR
You could try some of the undocumented DBCC commands to force the data.
Good luck.|||This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"
You have commas at the beginning and end of your date? You have three double-quotes delimiting two values? You have trailing spaces after your first field? Is your date a string?
We can't diagnose the problem like this.
Please take the time to post correct data with a complete and careful description if you want correct answers.
This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"Can you select that row with this where clause?
where date > '1/1/2050'|||No. where date > '1/1/2050' returns 'invalid date data'|||If there is no "key" on this table you could try:
Backup your database
Set RowCount 169440 -- Process only first nnnn rows.
Select * Into #TMP_Table from YourTable
Set RowCount 169441 -- Process only first nnnn rows.
Delete From YourTable -- Should delete all rows copied plus the bad one.
Set RowCount 0 -- Process all rows
Insert Into @.TMP_Table Select * From YourTable -- Copy the rest
This is a big "??". The "Select"s and "Delete"s may not process all rows in the same order, but they probably will. If nothing else works it is worth a try. Just make sure that you can restore from your backup!
OR
You could try some of the undocumented DBCC commands to force the data.
Good luck.|||This is the bad record. 2nd field is DATE
"0000000004015 ",15955-04-30 103:21:55.207000000,"
This is a good record immediately preceding the bad one.
"0000000004015 ",2006-01-22 00:00:00,"
You have commas at the beginning and end of your date? You have three double-quotes delimiting two values? You have trailing spaces after your first field? Is your date a string?
We can't diagnose the problem like this.
Please take the time to post correct data with a complete and careful description if you want correct answers.
How to delete large number of record without matter with transaction log?
Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei Cheong
Vyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>
|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>
|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...[vbcol=seagreen]
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I
>
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei Cheong
Vyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>
|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>
|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...[vbcol=seagreen]
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I
>
How to delete large number of record without matter with transaction log?
Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>[/vbcol]|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I[vbcol=seagreen]
>
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>[/vbcol]|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I[vbcol=seagreen]
>
How to delete large number of record without matter with transaction log?
Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> It happens to me that I have control over a MSSQL 2000 server that
>> was
>> looked after by another staff today. And than there is a call from the
>> server's owner company that the server's harddisk is running out of
>> space.
>> As background information, the SQL server's running on a 5GB HDD
>> partition with the Win2k system dir on it. And after some inspection, I
>> found the "msdb" database occupies about 1.13GB and it's transaction log
>> occupies 2MB.
>> So I opened Enterprise Manager and looked into the tables one by one.
>> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
>> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
>> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
>> cannot finish the task because the disk is running out of space and no
> room
>> for transaction log. And when I see the file size, the transaction log
> file
>> of msdb has grown to over 200MB. Oops.
>> At least, I managed to found an unoccupied machine to install a temp
> SQL
>> server, copy the database file to, trancate, and put back to the
>> origional
>> server. And the story is over. But what should I do if I experienced that
>> next time? There's management change in both my company and that company
> so
>> replacement of harddisk may not be feasible in a short time. And I'd like
> to
>> know if there's anything wrong in my procedure of handling the issue.
>> This
>> is my first time to handle a SQL server, and I have tough time on this. I
>> used to be a programmer only.
>> Looking forward for any advice. Thanks a lot.
>> Regards,
>> Lau Lei Cheong
>>
>|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> > Vyas's example shows how to divide a "big" transaction into a small ones
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > /*
> > DELETION here and Don't forget WHERE condition
> > */
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> > CHECKPOINT --Or doing BACKUP LOG File
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> > news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> It happens to me that I have control over a MSSQL 2000 server that
> >> was
> >> looked after by another staff today. And than there is a call from the
> >> server's owner company that the server's harddisk is running out of
> >> space.
> >>
> >> As background information, the SQL server's running on a 5GB HDD
> >> partition with the Win2k system dir on it. And after some inspection, I
> >> found the "msdb" database occupies about 1.13GB and it's transaction
log
> >> occupies 2MB.
> >>
> >> So I opened Enterprise Manager and looked into the tables one by
one.
> >> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> >> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the
"Query
> >> Analyzer"(I've explicitly called it to run the SQL statement) tell me
it
> >> cannot finish the task because the disk is running out of space and no
> > room
> >> for transaction log. And when I see the file size, the transaction log
> > file
> >> of msdb has grown to over 200MB. Oops.
> >>
> >> At least, I managed to found an unoccupied machine to install a
temp
> > SQL
> >> server, copy the database file to, trancate, and put back to the
> >> origional
> >> server. And the story is over. But what should I do if I experienced
that
> >> next time? There's management change in both my company and that
company
> > so
> >> replacement of harddisk may not be feasible in a short time. And I'd
like
> > to
> >> know if there's anything wrong in my procedure of handling the issue.
> >> This
> >> is my first time to handle a SQL server, and I have tough time on this.
I
> >> used to be a programmer only.
> >>
> >> Looking forward for any advice. Thanks a lot.
> >>
> >> Regards,
> >> Lau Lei Cheong
> >>
> >>
> >>
> >
> >
>
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> It happens to me that I have control over a MSSQL 2000 server that
>> was
>> looked after by another staff today. And than there is a call from the
>> server's owner company that the server's harddisk is running out of
>> space.
>> As background information, the SQL server's running on a 5GB HDD
>> partition with the Win2k system dir on it. And after some inspection, I
>> found the "msdb" database occupies about 1.13GB and it's transaction log
>> occupies 2MB.
>> So I opened Enterprise Manager and looked into the tables one by one.
>> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
>> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
>> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
>> cannot finish the task because the disk is running out of space and no
> room
>> for transaction log. And when I see the file size, the transaction log
> file
>> of msdb has grown to over 200MB. Oops.
>> At least, I managed to found an unoccupied machine to install a temp
> SQL
>> server, copy the database file to, trancate, and put back to the
>> origional
>> server. And the story is over. But what should I do if I experienced that
>> next time? There's management change in both my company and that company
> so
>> replacement of harddisk may not be feasible in a short time. And I'd like
> to
>> know if there's anything wrong in my procedure of handling the issue.
>> This
>> is my first time to handle a SQL server, and I have tough time on this. I
>> used to be a programmer only.
>> Looking forward for any advice. Thanks a lot.
>> Regards,
>> Lau Lei Cheong
>>
>|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> > Vyas's example shows how to divide a "big" transaction into a small ones
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > /*
> > DELETION here and Don't forget WHERE condition
> > */
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> > CHECKPOINT --Or doing BACKUP LOG File
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> > news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> It happens to me that I have control over a MSSQL 2000 server that
> >> was
> >> looked after by another staff today. And than there is a call from the
> >> server's owner company that the server's harddisk is running out of
> >> space.
> >>
> >> As background information, the SQL server's running on a 5GB HDD
> >> partition with the Win2k system dir on it. And after some inspection, I
> >> found the "msdb" database occupies about 1.13GB and it's transaction
log
> >> occupies 2MB.
> >>
> >> So I opened Enterprise Manager and looked into the tables one by
one.
> >> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> >> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the
"Query
> >> Analyzer"(I've explicitly called it to run the SQL statement) tell me
it
> >> cannot finish the task because the disk is running out of space and no
> > room
> >> for transaction log. And when I see the file size, the transaction log
> > file
> >> of msdb has grown to over 200MB. Oops.
> >>
> >> At least, I managed to found an unoccupied machine to install a
temp
> > SQL
> >> server, copy the database file to, trancate, and put back to the
> >> origional
> >> server. And the story is over. But what should I do if I experienced
that
> >> next time? There's management change in both my company and that
company
> > so
> >> replacement of harddisk may not be feasible in a short time. And I'd
like
> > to
> >> know if there's anything wrong in my procedure of handling the issue.
> >> This
> >> is my first time to handle a SQL server, and I have tough time on this.
I
> >> used to be a programmer only.
> >>
> >> Looking forward for any advice. Thanks a lot.
> >>
> >> Regards,
> >> Lau Lei Cheong
> >>
> >>
> >>
> >
> >
>
Friday, February 24, 2012
How to delete duplicate rows
Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
How to delete duplicate rows
Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
How to delete duplicate rows
Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> > Hi,
> > Currently we are having one large table in that we are having more
> than
> > 1 lacs record but most of the records are duplicates(All columns are
> having
> > same values).
> > How can i delete the particular duplicate row?
> >
> > Please give me a solution as soon as possible
> >
> > Thanks,
> > Herbert
>
>
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> > Hi,
> > Currently we are having one large table in that we are having more
> than
> > 1 lacs record but most of the records are duplicates(All columns are
> having
> > same values).
> > How can i delete the particular duplicate row?
> >
> > Please give me a solution as soon as possible
> >
> > Thanks,
> > Herbert
>
>
How to delete duplicate record
I have table by mistake i have lot of duplicate records. How do i delete it
.?
Thanks
Jayhttp://www.aspfaq.com/2431
Then
http://www.aspfaq.com/2509
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Jay,
Could you post the structure of your table. It would be helpful to know the
column names and primary key columns involved.
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Frank
Table looks like this
cbbdacc_account_id --> PK
cbbdacc_desc
cbbdacc_resp_pidm
cbbdacc_balance
-Jay
"Frank Castora" <fccsql@.hotmail.com> wrote in message
news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
> Jay,
> Could you post the structure of your table. It would be helpful to know
> the column names and primary key columns involved.
> Thanks,
> Frank Castora
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>|||> cbbdacc_account_id --> PK
Is this an IDENTITY column? Do you need to maintain existing values? If
so, how do you decide which ID # you need to keep?
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
Is a row considered a "duplicate" when all three of these columns are
identical in the two rows, or some subset?|||I respectfully defer to Aaron, as the articles he pointed you too are quite
sufficient. :)
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:%23AN4KionFHA.4056@.TK2MSFTNGP10.phx.gbl...
> Frank
> Table looks like this
> cbbdacc_account_id --> PK
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
>
> -Jay
>
> "Frank Castora" <fccsql@.hotmail.com> wrote in message
> news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
>|||Hi Aaron,
You may want to add:
WITH JustDups AS
(
SELECT * FROM T1 AS A
WHERE surkey <
(SELECT MAX(surkey) FROM T1 AS B
WHERE B.wannabekey = A.wannabekey)
)
DELETE FROM JustDups;
:)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zpiXaonFHA.1968@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2431
> Then
> http://www.aspfaq.com/2509
>
>
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
.?
Thanks
Jayhttp://www.aspfaq.com/2431
Then
http://www.aspfaq.com/2509
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Jay,
Could you post the structure of your table. It would be helpful to know the
column names and primary key columns involved.
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Frank
Table looks like this
cbbdacc_account_id --> PK
cbbdacc_desc
cbbdacc_resp_pidm
cbbdacc_balance
-Jay
"Frank Castora" <fccsql@.hotmail.com> wrote in message
news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
> Jay,
> Could you post the structure of your table. It would be helpful to know
> the column names and primary key columns involved.
> Thanks,
> Frank Castora
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>|||> cbbdacc_account_id --> PK
Is this an IDENTITY column? Do you need to maintain existing values? If
so, how do you decide which ID # you need to keep?
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
Is a row considered a "duplicate" when all three of these columns are
identical in the two rows, or some subset?|||I respectfully defer to Aaron, as the articles he pointed you too are quite
sufficient. :)
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:%23AN4KionFHA.4056@.TK2MSFTNGP10.phx.gbl...
> Frank
> Table looks like this
> cbbdacc_account_id --> PK
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
>
> -Jay
>
> "Frank Castora" <fccsql@.hotmail.com> wrote in message
> news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
>|||Hi Aaron,
You may want to add:
WITH JustDups AS
(
SELECT * FROM T1 AS A
WHERE surkey <
(SELECT MAX(surkey) FROM T1 AS B
WHERE B.wannabekey = A.wannabekey)
)
DELETE FROM JustDups;
:)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zpiXaonFHA.1968@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2431
> Then
> http://www.aspfaq.com/2509
>
>
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
How to delete and then add to the bottom of the list
Hi,
I have a record in the Database. I want to perform some functions on the record and then place that record at the end of list.
How can I achieve that ??are you trying to do this from a stored proc ? if so you can get the columnvalues into variables and then insert back into the db..
you can also use the "deleted" table to get the values...but there are some limitations i believe...you can check the documentation for using the deleted table..
HTH|||Data in SQL is, by definition, not sorted unless you specify how you want it sorted. So, if you want a record to be at the end of aset of records, you need to specify an ORDER BY that will put it at the end. If you always want the last record modified to be at the end, you might consider adding a LastModified datetime field to the table, putting in an UPDATE trigger that will set that field to the current date/time (calling GetDate will retrieve the current system date/time), and then have and ORDER BY LastModified on your SELECT command.
how to delete a specific record inside a DB?
In one of our DB's, we've got a corrupted record. My question to this group,
is there a command to delete a specific record out of the DB? If so, what
all information do i have to provide to the command, so it specifies this
record. Any help is greatly appreciated
Ken Zimmerman
MIS Dept
American Red Cross
The DELETE statement deletes a row identified by its column values. If you
aren't sure what the key columns of the table are then before you delete
anything you'll want to be sure you've found the right row(s). Check by
using a SELECT statement to view the data you are going to delete. For
example:
SELECT *
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
Once you're happy that you've defined the correct criteria for the row(s)
you want to delete:
DELETE
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
May be wise also to make sure you have a recent backup before deleting
anything.
David Portas
SQL Server MVP
|||KZimmerman wrote:
> In one of our DB's, we've got a corrupted record. My question to
> this group, is there a command to delete a specific record out of the
> DB? If so, what all information do i have to provide to the command,
> so it specifies this record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
I assume by corrupted, you mean that there is strange data in one of the
columns and you believe this to be a "bad" data issue, not a corruption
in the database.
If so, use David's recommendation and locate the primary key for the row
or rows with the problem so you can issue a delete statement to remove
them from the database.
If this is a database corruption issue, try issuing DBC CHECKDB on the
database to check for problems.
David G.
|||If none of that works. you might back up your database and try.
Dbcc checkdb repair_allow_data_loss (read about this in books on line first)
or
export the rows out using bcp ( you'll get the rows you can see)
truncate the table and re-import them (Be careful to get all of the rows ,
you might have to do some tricks to select forward (and it dies when you get
to the bad row, then select backwards to get the rows on the other side.)
Also,
Call MS Tech support, I think they have some tools which might be useful I
think their fee is $250... Not much considering the time you might spend
messing with this ( if the data is important.)
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
"KZimmerman" <KZimmerman@.discussions.microsoft.com> wrote in message
news:1087034D-3D76-4827-9166-EA3AB7D6CE91@.microsoft.com...
> In one of our DB's, we've got a corrupted record. My question to this
group,
> is there a command to delete a specific record out of the DB? If so, what
> all information do i have to provide to the command, so it specifies this
> record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
is there a command to delete a specific record out of the DB? If so, what
all information do i have to provide to the command, so it specifies this
record. Any help is greatly appreciated
Ken Zimmerman
MIS Dept
American Red Cross
The DELETE statement deletes a row identified by its column values. If you
aren't sure what the key columns of the table are then before you delete
anything you'll want to be sure you've found the right row(s). Check by
using a SELECT statement to view the data you are going to delete. For
example:
SELECT *
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
Once you're happy that you've defined the correct criteria for the row(s)
you want to delete:
DELETE
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
May be wise also to make sure you have a recent backup before deleting
anything.
David Portas
SQL Server MVP
|||KZimmerman wrote:
> In one of our DB's, we've got a corrupted record. My question to
> this group, is there a command to delete a specific record out of the
> DB? If so, what all information do i have to provide to the command,
> so it specifies this record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
I assume by corrupted, you mean that there is strange data in one of the
columns and you believe this to be a "bad" data issue, not a corruption
in the database.
If so, use David's recommendation and locate the primary key for the row
or rows with the problem so you can issue a delete statement to remove
them from the database.
If this is a database corruption issue, try issuing DBC CHECKDB on the
database to check for problems.
David G.
|||If none of that works. you might back up your database and try.
Dbcc checkdb repair_allow_data_loss (read about this in books on line first)
or
export the rows out using bcp ( you'll get the rows you can see)
truncate the table and re-import them (Be careful to get all of the rows ,
you might have to do some tricks to select forward (and it dies when you get
to the bad row, then select backwards to get the rows on the other side.)
Also,
Call MS Tech support, I think they have some tools which might be useful I
think their fee is $250... Not much considering the time you might spend
messing with this ( if the data is important.)
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
"KZimmerman" <KZimmerman@.discussions.microsoft.com> wrote in message
news:1087034D-3D76-4827-9166-EA3AB7D6CE91@.microsoft.com...
> In one of our DB's, we've got a corrupted record. My question to this
group,
> is there a command to delete a specific record out of the DB? If so, what
> all information do i have to provide to the command, so it specifies this
> record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
Subscribe to:
Posts (Atom)