how to Diff vs Sum in Group By query?

Hello,
if area 'A' contains 2 numbers in 2 rows then
Select area, Sum(number) from tbl1 where area = 'A'
Group By area
gives me the sum of these 2 numbers in area 'A'
But how can I retrieve the difference of these 2 numbers
using T-Sql?
Thanks,
RonRon wrote:
> Hello,
> if area 'A' contains 2 numbers in 2 rows then
> Select area, Sum(number) from tbl1 where area = 'A'
> Group By area
> gives me the sum of these 2 numbers in area 'A'
> But how can I retrieve the difference of these 2 numbers
> using T-Sql?
> Thanks,
> Ron
Max(number) - Min(number)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks. That is pretty . I forgot to include a
twist in here. I also have a datefld. So num1 may be min
or num may be max. I have to show +num or -num.
select area, (First(num) - Last(num)) as num1 from tbl1
where datefld between '1/1/2005' and 1/2/2005' Group By
area having area = 'A'
I was able to use your trick to get my positive or
negative result using First and Last functions. Any
suggestions appreciated if this is incorrect usage.
Thanks again,
Ron

>--Original Message--
>Ron wrote:
>Max(number) - Min(number)
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Ron wrote:
> Thanks. That is pretty . I forgot to include a
> twist in here. I also have a datefld. So num1 may be min
> or num may be max. I have to show +num or -num.
> select area, (First(num) - Last(num)) as num1 from tbl1
> where datefld between '1/1/2005' and 1/2/2005' Group By
> area having area = 'A'
> I was able to use your trick to get my positive or
> negative result using First and Last functions. Any
> suggestions appreciated if this is incorrect usage.
First? Last? You must be using Access ... This is a SQL Server group
Do you need a SQL Server (Transact-SQL) solution? Those fnctions do not
exist in T-SQL.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:

>Thanks. That is pretty . I forgot to include a
>twist in here. I also have a datefld. So num1 may be min
>or num may be max. I have to show +num or -num.
>select area, (First(num) - Last(num)) as num1 from tbl1
>where datefld between '1/1/2005' and 1/2/2005' Group By
>area having area = 'A'
>I was able to use your trick to get my positive or
>negative result using First and Last functions. Any
>suggestions appreciated if this is incorrect usage.
Hi Ron,
Try if this helps:
SELECT G.Area, F.num - L.num AS num1
FROM (SELECT area, MIN(datefld) AS FDate, MAX(datefld) AS LDate
FROM tbl1
WHERE datefld BETWEEN '20050101' AND '20050201'
GROUP BY area) AS G
INNER JOIN tbl1 AS F
ON F.area = G.area
AND F.datefld = G.FDate
INNER JOIN tbl1 AS L
ON L.area = G.area
AND L.datefld = G.LDate
WHERE G.area = 'A'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||SELECT A.area, A.num-B.num
FROM Tbl1 AS A
JOIN Tbl1 AS B
ON A.datefld < B.datefld
AND A.area = 'A'
AND B.area = 'A'
David Portas
SQL Server MVP
--|||Yes, I figured that out. I was writing the sql in Access
and transferring in to Query Analyzer. Sorry bout that.
I did end up creating a udf for first and last.

>--Original Message--
>Ron wrote:
min
>First? Last? You must be using Access ... This is a SQL
Server group
>Do you need a SQL Server (Transact-SQL) solution? Those
fnctions do not
>exist in T-SQL.
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed
in my From
>header is my spam trap, so I don't check it very often.
You will get a
>quicker response by posting to the newsgroup.
>
>.
>|||Thanks. I will give that a try.

>--Original Message--
>On Fri, 25 Feb 2005 11:19:41 -0800, Ron wrote:
>
min
>Hi Ron,
>Try if this helps:
>SELECT G.Area, F.num - L.num AS num1
>FROM (SELECT area, MIN(datefld) AS FDate, MAX
(datefld) AS LDate
> FROM tbl1
> WHERE datefld BETWEEN '20050101'
AND '20050201'
> GROUP BY area) AS G
>INNER JOIN tbl1 AS F
> ON F.area = G.area
> AND F.datefld = G.FDate
>INNER JOIN tbl1 AS L
> ON L.area = G.area
> AND L.datefld = G.LDate
>WHERE G.area = 'A'
>
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks very much for your reply. I will give this a try.

>--Original Message--
>SELECT A.area, A.num-B.num
> FROM Tbl1 AS A
> JOIN Tbl1 AS B
> ON A.datefld < B.datefld
> AND A.area = 'A'
> AND B.area = 'A'
>--
>David Portas
>SQL Server MVP
>--
>.
>

How to Determine, how much recordsets returns Query?

Hi all!

Is there any chanse to determine in Transact-SQL, how much recordsets/rows already returned by currently executing query? I don't need count of rows affected by last statement (that @.@.ROWCOUNT returns), but ones, really returned to SQL-Client.

To understand, what I need it for, please see: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1707794&SiteID=1#1715230

Solution found!

See: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1716062&SiteID=1&mode=1#1716062

How to detect if column data changed and know prev. and new value

I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

BillHi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/d...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <billtepe@.mssonline.net> wrote in message
news:7364847c.0309060600.7023b89a@.posting.google.c om...
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.
> Is there a "simple" way to do this? I know I could query the table
> before the update and compare to what the new value is and react
> accordingly.
> Just wondering if there is something nifty in Sql Server that I am
> missing that could help me with this.
> Thanks in advance for your help.
> Bill|||[posted and mailed, please reply in news]

Bill Tepe (billtepe@.mssonline.net) writes:
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.

In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.

Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.

You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:

select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted

Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel. www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How to Design and Handle a Table with 30 Million rows

Hi

my requirement is like this - I need to maintain a subscription DB with more then 30 million rows in sql server thet keeps on increasing. I need to Update ,Insert and select from the Table. Problem is this has to be integrated with existing application where performance should not get effected. In this table application id and user number are unique.

Can anyone suggest what should be the approach.with minimum effect on performance of existig application.

ThanksIf you have proper indexes it should work with no problem.

Writing new queries could be challenging and will require more indexes. But with proper indexing you can manage it too.

Don't create clustered index if possible it will slow down update\insert and will require plenty free space on a Server.

Control updated number of records update, never update all table at once, it could bust transaction log and stop a Server.|||Can i select distinct values of a column from such a large table without using select distinct. Because this query runs very slowly on such a large table.|||Try to put index over this column.
In this case only index will be searched and not table itself.

Good Luck.

How to delete rows in tables...

I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database.

Im using SQL 2000

I have tried with the following:

Delete from fsalesinvoiceline

Join dsalesinvoiceheader on

Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and

Fsalesinvoiceline.company= dsalesinvoiceheader.company

Where dsalesinvoiceheader.billtocustomerno=’INDTAST DEBITORNUMMER’

Go

Delete from dsalesinvoiceheader

Where dsalesinvoiceheader.billtocustomerno=’INDTAST DEBITORNUMMER’

I get the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN'

What am I doing wrong?

/S?ren D. Jensen

Hello,

You could try to use Microsoft SQL Server Management Studio to open the table and try to delete frm the UI.

Thanks,

|||

sdj_dk wrote:

I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database.

Im using SQL 2000

I have tried with the following:

Delete from fsalesinvoiceline

Join dsalesinvoiceheader on

Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and

Fsalesinvoiceline.company= dsalesinvoiceheader.company

Where dsalesinvoiceheader.billtocustomerno=’INDTAST DEBITORNUMMER’

Go

Delete from dsalesinvoiceheader

Where dsalesinvoiceheader.billtocustomerno=’INDTAST DEBITORNUMMER’

I get the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN'

What am I doing wrong?

/S?ren D. Jensen

BE ULTRA CAREFUL!!! - I would suggest a backup or at least a test on a staging system if available.

DELETE
FROM
fsalesinvoiceline, dsalesinvoiceheader
WHERE
fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid
AND dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

|||

Barry Andrew wrote:

BE ULTRA CAREFUL!!! - I would suggest a backup or at least a test on a staging system if available.

DELETE
FROM
fsalesinvoiceline, dsalesinvoiceheader
WHERE
fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid
AND dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

Like I said the data in these tables are copies from our OLTP database (MBS Navision) they are emptied every night and filled with new data, so nothing will get lost if something goes wrong. I will try your suggestion tomorrow.

|||

I have another question on how to delete specific rows from a table.

The table is called ProdTaskLine

I need to delete rows where:

[Cost Type] have the value 1

[Item Type] have the value 1

[Line Type] have the value 0

And last but not least the first character in the field [Cost No] have to be different from the letter 'B'

I am a total newbie to this and have no idear how to code this.

|||Ok we can work this out also. But has the above solved your original posts problem?|||

Barry Andrew wrote:

Ok we can work this out also. But has the above solved your original posts problem?

I haven't had the time to look at it yet - had a datetime error this morning that I need to solve first so we can use our cubes. :) I will post again when I have tested your surgestion.

|||

It seems like your are trying to dele from both tables at once. When doing this I get this error message:

Line 1: Incorrect syntax near ','

I then tried only deleting from fSalesInvoiceLine with this code:

DELETE FROM fSalesInvoiceLine
WHERE (fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid) AND (Fsalesinvoiceline.company= dsalesinvoiceheader.company) AND (dsalesinvoiceheader.billtocustomerno = 40000)

This give the following error message:

The column prefix 'dsalesinvoiceheader' does not match with a table name or alias name used in the query

Have no idear what to do next? I have checked all the table and column names.

|||

ok lets just try,

DELETE f, d
FROM fsalesinvoiceline AS f, dsalesinvoiceheader AS d
WHERE
f.salesid = d.salesid
AND d.billtocustomer = 'INDTAST DEBITORNUMMER'

|||

Barry Andrew wrote:

ok lets just try,

DELETE f, d
FROM fsalesinvoiceline AS f, dsalesinvoiceheader AS d
WHERE
f.salesid = d.salesid
AND d.billtocustomer = 'INDTAST DEBITORNUMMER'

Still get this error message: "Line 1: Incorrect syntax near ',' "

Am I doing this right? To test the code I use SQL Server Enterprice Manager - right click on the table fSalesInvoiceLine and choose Query...

|||YOu can only delete from one table at a time. So either specify fsalesinvoiceline or dsalesinvoiceheader.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens Suessmeyer,

I thought that providing we structure the query correctly, a delete with a join was permitted?

Im working on this off the following reference; http://msdn2.microsoft.com/en-us/library/aa258847(SQL.80).aspx

|||

Barry Andrew wrote:

ok lets just try,

DELETE f, d
FROM fsalesinvoiceline AS f, dsalesinvoiceheader AS d
WHERE
f.salesid = d.salesid
AND d.billtocustomer = 'INDTAST DEBITORNUMMER'

Can we finally try;

DELETE
fsalesinvoiceline
FROM
fsalesinvoiceline
INNERJOIN
dsalesinvoiceheader ON fsalesinvoiceline.salesid = fsalesinvoiceline.salesid
WHERE
dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

It looks bizzarre, but I think it looks like this should work.

Barry Andrew

|||

Sure it is, but the query you pointed out below is finally not correct:

DELETE
fsalesinvoiceline
FROM
fsalesinvoiceline
INNER JOIN
dsalesinvoiceheader ON fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid
--(guess you wanted to join with the dsalesinvoiceheader)
WHERE
dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Sure it is, but the query you pointed out below is finally not correct:

DELETE
fsalesinvoiceline
FROM
fsalesinvoiceline
INNER JOIN
dsalesinvoiceheader ON fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid
--(guess you wanted to join with the dsalesinvoiceheader)
WHERE
dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER'

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Ah I see now! doh!

thanks for pointing it out

How To Delete Rows In SQL

Does the SQL DELETE command actually delete records or just mark them for deletion.

And if so, how do you force a physical deletion? (code required). Thanks.

Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("DELETE FROM Table WHERE Day=" & "Monday", connection)

connection.Open()

command.ExecuteNonQuery()

connection.Close()

Yes, the delete command physically deletes data.|||

It's funny how my dB size increased after I ran the Delete command.

It's up 29MB, but I'm not sure how many records I deleted and what's the root cause of the increase and what's in that increase. I did do a test preview and it did reflect the correct data. Oh well.

|||

at first u execute DBCC SHRINKDATABASE command.

Then you check your database size.

|||Do you have an example... wouldn't want to ruin a database...|||Just use the command mentioned above:

DBCC SHRINKDatabase(<name>,<RestofthecurrentSize>)

Information about that can be found int he BOL (Books Online, the help of SQL Server)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I wouldn't just arbitrarily shrink the database.

First of all, you should understand that your database is made of 2 types of files, data files and log files. 1 of each by default.

When you delete data, it physically deletes it from the data file, but the data file is not resized, it just has more free space. Meanwhile, the delete transaction is written to your log file. If your log file was full at the time of the transaction, the log file is expanded to make room. The file isn't grown just enough to fit the new transaction, it is expanded by a pre-determined amount. By default, your log file expands by 10% when it needs to expand. So if the log file is 100 MB, it expands by 10 MB to have a new size of 110MB. If your log file is 10 GB, it expands by 1 GB to a size of 11 GB.

Data and log file size is very important. It is key to keep enough free space in them that the files do not need to be resized frequently. Resizing files, whether expanding or shrinking, has a big impact on performance. You want to avoid resizing arbitrarily. And you want to limit sizing when possible.

How to delete rows in a table when no primary key is defined

Hello,

I want to delete duplicate rows in a table when no primary key is
defined.
For eg: If we have table1 with data as below,

Suma 23 100
Suma 23 100

I want to delete a row from this table and retain only one row.

I tried deleting self joins and exists operator. But it is deleting
both the rows. I want to retain one row.

Can anybody help me out.

Thanks in advance,
Suma

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict221110.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520Add a identity column to the table and delete the row with de min value.|||Patarroxa wrote:
> Add a identity column to the table and delete the row with de min
> value.

Or copy the data with a SELECT DISTINCT into another table, drop the
original and rename the new table.

robert|||Create a new table (with a key), then use SELECT DISTINCT or GROUP BY to
populate it from the old one.

--
David Portas
SQL Server MVP
--|||"suma" wrote:
> Hello,
> I want to delete duplicate rows in a table when no primary key
> is defined.
> For eg: If we have table1 with data as below,
> Suma 23 100
> Suma 23 100
> I want to delete a row from this table and retain only one
> row.
> I tried deleting self joins and exists operator. But it is
> deleting both the rows. I want to retain one row.
> Can anybody help me out.
> Thanks in advance,
> Suma

Thanks for the response.
But it has to be done using a single sql statement.
Using multiple we can do it...is there any way to do using a single
sql statement.
Thanks,
Suma|||First of all this is not a table by definition. A table must have a
key. And the answer is No, it will take more than one statement to
clean up the base table -- either a cursor, an IDENTITY or a SELECT
DISTINCT. You can put the SELECT DISTINCT into a VIEW as a kludge.

You did fire the guy that did this, didn't you?|||Using a single DELETE statement it can't be done if there is no way to
differentiate between the rows. That's why a primary key is supposed to
be mandatory. Why should you have a table without a key?

--
David Portas
SQL Server MVP
--|||You're not really saving anything doing it this way over the alternatives,
and it's *very* slow for large numbers of duplicates.

SET ROWCOUNT=1

DELETE table1
WHERE EXISTS (SELECT *
FROM table1 AS t2
WHERE table1.col1 = t2.col1 and table1.col2 = t2.col2 and table1.col3 =
t2.col3
GROUP BY t2.col1, t2.col2, t2.col3
HAVING COUNT(*) > 1)

WHILE @.@.ROWCOUNT>0
DELETE ... --same statement all over

Disclaimer: This is not tested. I am not responsible for any loss of data
incurred by use of this technique. SELECT INTO with GROUP BY is probably
safest and fastest, as recommended by others.

Also see books online, Index, DELETE (described), and the description of
DELETE FROM table WHERE CURRENT OF cursor_name

"suma" <DoNotEmail@.dbForumz.com> wrote in message
news:4_761872_6b47fee83970ff4272fca067cae7180d@.dbf orumz.com...
> "suma" wrote:
> > Hello,
> > I want to delete duplicate rows in a table when no primary key
> > is defined.
> > For eg: If we have table1 with data as below,
> > Suma 23 100
> > Suma 23 100
> > I want to delete a row from this table and retain only one
> > row.
> > I tried deleting self joins and exists operator. But it is
> > deleting both the rows. I want to retain one row.
> > Can anybody help me out.
> > Thanks in advance,
> > Suma
> Thanks for the response.
> But it has to be done using a single sql statement.
> Using multiple we can do it...is there any way to do using a single
> sql statement.
> Thanks,
> Suma|||And thats why oracle we can delete the duplicate rows using rowid or rownum
and not in sql server. Some unique identity has to be there !!|||True, but with correct design you'll never need to. The problem IS
soluble in SQL Server too, it's just that SQL Server requires that you
fix things rather than allow you to live with such a kludgy solution.

--
David Portas
SQL Server MVP
--|||>> Oracle we can delete the duplicate rows using rowid or rownum and
not in sql server <<

Yes, Oracle is a sequential file system and a piss-poor RDBMS under the
covers. Parallelism, set processing, and all the other things that
allow a good SQL implmentation to run 4 to 5 orders of magnitude faster
and 80-90% smaller are not available in Oracle and cannot be because of
a horrible architecture. Look up the performance for Nucleus (Sand
Technology) and other VLDB products.|||There is a way to do this, but it will take massive amounts of time to
delete many rows, because the duplicate rows are deleted one row at a
time. It goes like this:

SET ROWCOUNT 1
-- Generate a rowcount > 0
SELECT COUNT(*) FROM MyTable
While @.@.rowcount > 0
Begin
DELETE MyTable
WHERE (
SELECT COUNT(*)
FROM MyTable T1
WHERE T1.Col1 = MyTable.Col1
AND T2.Col2 = MyTable.Col2
) > 1
End
-- don't forget this line!
SET ROWCOUNT 0

Hope this helps,
Gert-Jan

suma wrote:
> Hello,
> I want to delete duplicate rows in a table when no primary key is
> defined.
> For eg: If we have table1 with data as below,
> Suma 23 100
> Suma 23 100
> I want to delete a row from this table and retain only one row.
> I tried deleting self joins and exists operator. But it is deleting
> both the rows. I want to retain one row.
> Can anybody help me out.
> Thanks in advance,
> Suma
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL: http://www.dbforumz.com/General-Dis...pict221110.html
> Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=760520

How to delete rows from a 15 million row table

Using SQL 2000, I'm trying to trim down a 15 million row table. I initially tried simply doing a DELETE FROM TABLE WHERE DATE < '10/04/2002'. The initial start date for the table was in august so I figured this would account for about 1/3 of its contents, assuming an even daily amount was being inserted.

However, this query would need to go through the entire table to successfully perform and I had to cancel the query due to it taking too long and nearly taking down the sql server due to all the hd crunching.

Then I tried deleting it in batches...by using set rowcount = 10000, then sticking that delete statement into a nested loop and having it issue a checkpoint whenever it found a row to delete. This too, took forever just even through the first pass. Using Profiler, I watched the query get to the delete statement, then take forever again...and again I had to cancel it.

Is there a simpler way to do this, or am I missing something? Why would it take forever even for the first pass I would really like something that would export or backup the data first, then delete it.

EdHi,
y dont u try splitting ur 15 million row table into some 10 or 15 temporary tables, delete the records in the temporary tables and then group it back to the main table??,the Split-table approach??
Regards,
Ramya

Originally posted by KungFuJoe
Using SQL 2000, I'm trying to trim down a 15 million row table. I initially tried simply doing a DELETE FROM TABLE WHERE DATE < '10/04/2002'. The initial start date for the table was in august so I figured this would account for about 1/3 of its contents, assuming an even daily amount was being inserted.

However, this query would need to go through the entire table to successfully perform and I had to cancel the query due to it taking too long and nearly taking down the sql server due to all the hd crunching.

Then I tried deleting it in batches...by using set rowcount = 10000, then sticking that delete statement into a nested loop and having it issue a checkpoint whenever it found a row to delete. This too, took forever just even through the first pass. Using Profiler, I watched the query get to the delete statement, then take forever again...and again I had to cancel it.

Is there a simpler way to do this, or am I missing something? Why would it take forever even for the first pass I would really like something that would export or backup the data first, then delete it.

Ed|||I had a similar problem on an older server. There I had not enough space to make a copy of the data i had to keep on the database. So I made a stored procedure which went through a cursor that deleted 10000 rows. Then I packed the procedure into an SQL-Task in a DTS, which i scheduled for running several times in the night.|||I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on. I need something that will work while the database is still up.

Ed

Originally posted by ramya
Hi,
y dont u try splitting ur 15 million row table into some 10 or 15 temporary tables, delete the records in the temporary tables and then group it back to the main table??,the Split-table approach??
Regards,
Ramya|||Do you have this stored procedure handy so I can look at it? I tried doing something similar (using set rowcount = ) as I described in my initial post, but I let it run for nearly 3 hours and it did not delete a single row.

Ed

Originally posted by austrian_ead
I had a similar problem on an older server. There I had not enough space to make a copy of the data i had to keep on the database. So I made a stored procedure which went through a cursor that deleted 10000 rows. Then I packed the procedure into an SQL-Task in a DTS, which i scheduled for running several times in the night.|||Originally posted by KungFuJoe
Do you have this stored procedure handy so I can look at it? I tried doing something similar (using set rowcount = ) as I described in my initial post, but I let it run for nearly 3 hours and it did not delete a single row.

Ed

I am sorry, I don't have it anymore, but the code was not complicated:

declare @.columnx integer
declare cur_test cursor for
select top 10000 0 as columnx from bigtable
for update
open cur_test
fetch cur_test into @.columnx
while @.@.fetch_status = 0
begin
delete from bigtable where current of cur_test
fetch cur_test into @.columnx
end
close cur_test
deallocate cur_test

it's like this, not exactly, it's only a draft. You should try it it with a small database for testing.|||Thanks for your help :)

I'll give it a shot.

Ed

Originally posted by austrian_ead
I am sorry, I don't have it anymore, but the code was not complicated:

declare @.columnx integer
declare cur_test cursor for
select top 10000 0 as columnx from bigtable
for update
open cur_test
fetch cur_test into @.columnx
while @.@.fetch_status = 0
begin
delete from bigtable where current of cur_test
fetch cur_test into @.columnx
end
close cur_test
deallocate cur_test

it's like this, not exactly, it's only a draft. You should try it it with a small database for testing.|||what's the secret there? bcp out the records you want to keep, alter table tbl nocheck constraint all, truncate, bcp in!|||Yeah, but he says the table is used 24/7 and it can't be down at all. It's heavy use is probably why the deletes are taking so long.

Maybe he should try turning off loging, deleting his records, and then turning it back on again and starting a new backup cycle.

Otherwise, I think his best bet is to keep deleteing them in small batches, and as separate transactions.

I don't see what he gets out of using a cursor for this.|||Originally posted by KungFuJoe
I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on. I need something that will work while the database is still up.
Ed

What is that there is no maint window?

Also, is there an index on the column?

Have you checked out sp_lock? what processes are going on?

Is this a web based app?

And you say 3 months is a 1/3 of the data...that's a lot of data AND I'm sure it's going to continue to grow...

You seriously need to understand the growth of this monster and plan an archival strategy...what type of data btw is ancient history after that period of time...don't you track a status or something...or is like phone records?

what does your app do?|||Actually, my understanding is that the database needs to be 24/7, not the table. Besides, the poster already mentioned making several attempts to delete from this table, during which it WAS inaccessible for anything other than SELECT with READ_UNCOMMITTED isolation level.|||Originally posted by KungFuJoe
I can't risk bringing this table down for any amount of time...|||I can't risk bringing this table down for any amount of time...this is mission critical 24/7 database that the table resides on . I need something that will work while the database is still up.

Ed|||Well if it isn't the SQL Server hardcore !!!

How you dudes doing ?

Here is a novel way of removing records from a HUGE table.

You could turn Select Into / Bulk Copy on

Perform a select into non-logged operation using a where clause to filter the records into another table. If you have an index on the column with the where clause on it then should be fast.

reindex the new table.

Perfom a two sp_renames of the two tables.

If you are prepared to have you db down for the time it takes perform the two sp_renames then you are jammin'!

Got to love those non - logged op's.|||Here we go again...and how this better? You forgot about recompiles that you'll have to do...got enough time to spare?|||Sorry mate,

I never said I had this in a stored proc.

Used to work in a large datawarehouse and found this ad hoc method was useful when I wanted to avoid using a delete.

Since this was the original question - I thought it might be an alternate option if he could handle downtime for the time of 2 sp_rename's

Cheers|||Don't apologize...unless the poster comes back with more info, everything is speculative...|||I'll second Aldo2003's tip to reduce the affect of downtime and to get the optimum performance one should arrange a downtime window to accomplish the task easily and fastly.

It would be ideal if downtime can be agreed by business and will be faster when no other process is accessing the database.

When you're getting something you should be ready to sacrifice something...:)|||So, you're all saying that "SELECT * INTO..." is faster than TRUNCATE?

Anyone for a test?|||No, they aren't trying to optimize the total process time, just the table down-time. So they aren't counting the SELECT because it doesn't lock up the table.

I think they are assuming that no new data will be added to the table between the SELECT INTO NEWTABLE and the RENAME, and I bet that this is not the case.|||Actually, SELECT will placed a shared lock on the table, unless you change the isolation level. But the point is that BCPing only records that you need paired with truncating the table is faster than any other solution, and they don't seem to see it that way. I, in turn, don't see anything else as an alternative. Downtime is a downtime is a downtime. It's just a matter of how much down, and ... what's the time? It's that time, when I go a have my mid-day smoke break off campus!!!|||You have to leave the campus?

This from a state where drinking and driving where basically legal up to a short while ago?|||Well, we got this mayor Garza, all fitness-oriented (I can still probably kick his butt without getting off the bar stool), and our glorious and victorious CEO decided to come up with a "we're doing you good" initiative by declaring it on the local news. Reality (we have our ways [needs to be pronounced with a heavy Russian accent]) shows that the company got a 10% insurance reduction for making it a non-smoking campus. So, smoking, non-smoking, or smoke up someone's a$$? That's the dilemma ;)|||Have they banned smoking in bars?

Did you know, NYC (The new Rome) has banned smoking in all bars throughout the entire city...

NEW YORK F____'N CITY!

I mean give me a break...

You can buy crack on the corner but no cig...|||Technically, I believe that crack has been illegal in NYC for quite a while...|||Originally posted by Brett Kaiser
This from a state where drinking and driving where basically legal up to a short while ago? What, they've closed the drive up windows on the liquor stores in Texas ? Say it isn't so!

-PatP|||Originally posted by blindman
Technically, I believe that crack has been illegal in NYC for quite a while... Ok, so what's your point here ?

-PatP|||Originally posted by Brett Kaiser
Have they banned smoking in bars?

Did you know, NYC (The new Rome) has banned smoking in all bars throughout the entire city...

NEW YORK F____'N CITY!

I mean give me a break...

You can buy crack on the corner but no cig...

<RANT>
Not just New York City...since last summer it's the whole state...thank god we built a shack behind our building to shelter us from this wonderful weather...to make matters worse NYS also added a line to the tax forms this year to make us pay the maximum amount of tax possible..they want us to estimate goods we purchased outside our county, on reservations (where we get our cigs), and online...*&$&^ politicians...

</RANT>|||Starting tomorrow, going to the gym

How about banning McDonalds, what's up with this no supersizing $%&#?|||Originally posted by rdjabarov

Starting tomorrow, going to the gym

How about banning McDonalds, what's up with this no supersizing $%&#?

..its all about cutting the budgets...actually i think they will move supersizing overseas (not to bring that subject up AGAIN)...|||that's a good one, here they went through 3 indians, with the same outcome. and how do you like an answer to a questions "what's a trigger?"

"it's when you move your mouse, because that's what oracle objects is all about"!!!

How to Delete Records that are Linked with Relationships

Hello,
I am writing to ask if someone can tell me what the
command is to delete rows in an SQL 2000 database that are
linked through a foreign key relationship.
For example, I have a row in a "Persons" table that has a
primary key "Person ID". "Person ID" is then a foreign
key in two other tables. I would like to be able to
delete a person row from the "Persons" table and then
automatically have all associated rows based on
that "Person ID" in the other two tables deleted.
Thanks in advance!
MikeIn the design for the Persons table, open the relationship and make sure
'cascade delete' is on. This should do what you're asking...
Hope this helps...
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:046101c35559$bd142450$a401280a@.phx.gbl...
> Hello,
> I am writing to ask if someone can tell me what the
> command is to delete rows in an SQL 2000 database that are
> linked through a foreign key relationship.
> For example, I have a row in a "Persons" table that has a
> primary key "Person ID". "Person ID" is then a foreign
> key in two other tables. I would like to be able to
> delete a person row from the "Persons" table and then
> automatically have all associated rows based on
> that "Person ID" in the other two tables deleted.
> Thanks in advance!
> Mike

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
>
>

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
>
>

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
>
>

How to delete all rows/truncate the target table

How to delete all rows from target table or truncate the target, before loading the fresh data into it?Use an Execute SQL Task before the data flow task and truncate the table using Truncate statement. There is no option in OLE DB or Sql Server Destination to truncate the table before loading new rows.

How to delete all rows in a table

I am running SQL Server 2000.
I need to delete all of the rows in a table within Enterprize management.
How can I do this. All I see is how to delete the table itself.
I only want to delete the data.
Ron
Mark the first line go to the last line with STRG+End, mark the last line
(that marks all) and press DEL.
Other option would be to swtich to SQL mode (in the SQL designer) and
manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
table SomeTable (which is not logged). You can also issue these commands in
the QA.
HTH, jens Suessmeyer.
"Ron" wrote:

> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize management.
> How can I do this. All I see is how to delete the table itself.
> I only want to delete the data.
> Ron
|||Thanks
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Mark the first line go to the last line with STRG+End, mark the last line
> (that marks all) and press DEL.
> Other option would be to swtich to SQL mode (in the SQL designer) and
> manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
> table SomeTable (which is not logged). You can also issue these commands in
> the QA.
> HTH, jens Suessmeyer.
> "Ron" wrote:
|||Ron wrote:
> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize
> management. How can I do this. All I see is how to delete the table
> itself.
> I only want to delete the data.
> Ron
The fastest way to do this as an administrator/db owner is to truncate
the table to avoid excessive logging, unless you want the operation
logged. You can do this from Query Analyzer or any query tool by issuing
a TRUNCATE TABLE <table_name>
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to delete all rows in a table

I am running SQL Server 2000.
I need to delete all of the rows in a table within Enterprize management.
How can I do this. All I see is how to delete the table itself.
I only want to delete the data.
RonMark the first line go to the last line with STRG+End, mark the last line
(that marks all) and press DEL.
Other option would be to swtich to SQL mode (in the SQL designer) and
manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
table SomeTable (which is not logged). You can also issue these commands in
the QA.
HTH, jens Suessmeyer.
"Ron" wrote:
> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize management.
> How can I do this. All I see is how to delete the table itself.
> I only want to delete the data.
> Ron|||Thanks
"Jens Sü�meyer" wrote:
> Mark the first line go to the last line with STRG+End, mark the last line
> (that marks all) and press DEL.
> Other option would be to swtich to SQL mode (in the SQL designer) and
> manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
> table SomeTable (which is not logged). You can also issue these commands in
> the QA.
> HTH, jens Suessmeyer.
> "Ron" wrote:
> > I am running SQL Server 2000.
> > I need to delete all of the rows in a table within Enterprize management.
> > How can I do this. All I see is how to delete the table itself.
> > I only want to delete the data.
> >
> > Ron|||Ron wrote:
> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize
> management. How can I do this. All I see is how to delete the table
> itself.
> I only want to delete the data.
> Ron
The fastest way to do this as an administrator/db owner is to truncate
the table to avoid excessive logging, unless you want the operation
logged. You can do this from Query Analyzer or any query tool by issuing
a TRUNCATE TABLE <table_name>
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to delete all rows in a table

I am running SQL Server 2000.
I need to delete all of the rows in a table within Enterprize management.
How can I do this. All I see is how to delete the table itself.
I only want to delete the data.
RonMark the first line go to the last line with STRG+End, mark the last line
(that marks all) and press DEL.
Other option would be to swtich to SQL mode (in the SQL designer) and
manipulate the SQl Statement as Follows: Delete From SomeTable --OR Truncate
table SomeTable (which is not logged). You can also issue these commands in
the QA.
HTH, jens Suessmeyer.
"Ron" wrote:

> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize management.
> How can I do this. All I see is how to delete the table itself.
> I only want to delete the data.
> Ron|||Thanks
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Mark the first line go to the last line with STRG+End, mark the last line
> (that marks all) and press DEL.
> Other option would be to swtich to SQL mode (in the SQL designer) and
> manipulate the SQl Statement as Follows: Delete From SomeTable --OR Trunca
te
> table SomeTable (which is not logged). You can also issue these commands i
n
> the QA.
> HTH, jens Suessmeyer.
> "Ron" wrote:
>|||Ron wrote:
> I am running SQL Server 2000.
> I need to delete all of the rows in a table within Enterprize
> management. How can I do this. All I see is how to delete the table
> itself.
> I only want to delete the data.
> Ron
The fastest way to do this as an administrator/db owner is to truncate
the table to avoid excessive logging, unless you want the operation
logged. You can do this from Query Analyzer or any query tool by issuing
a TRUNCATE TABLE <table_name>
David Gugick
Quest Software
www.imceda.com
www.quest.com

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?
ad
Perhaps you need to deal with DRI before your the script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>
|||Easiest way it probably to script the database (actually, you should have the schema as source code
already). Then drop the database and run the script file(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables.
> Have there any convenient way to do that?
>

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?ad
Perhaps you need to deal with DRI before your the script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>|||Easiest way it probably to script the database (actually, you should have th
e schema as source code
already). Then drop the database and run the script file(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables
.
> Have there any convenient way to do that?
>[/vbcol]

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?ad
Perhaps you need to deal with DRI before your the script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>|||Easiest way it probably to script the database (actually, you should have the schema as source code
already). Then drop the database and run the script file(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables.
> Have there any convenient way to do that?
>