Is this possible? Hiding headers if last page contains no rows

Hello everyone. This is my first post here. I have a report that contains a subreport displaying all the aggregate data. My report headings repeat on every page. If my last page contains no rows and only my subreport I do not want the headings to be visible. Now the part I can't get is how to write the condition

if the lastpage.rowcount = 0 then hideHeadings (pseudo)

Is it possible to ask what the rowcount is on the last page? This is the tricky part. Well I'm going to keep trying and if anyone has any solutions I will appreciate it very much.

does ur report contain any tables...?

|||

Yes it does all the data is displayed in a table. However the headings are just text boxes in the header area of the report. If there is a problem with tables I can easily switch to list boxes. Thanks.

|||

to ur table... add a table header row... and let it repeat on each page... so now only if there is data in the next page ur header would show up otherwise no..

Hope this helps.

Regards

KAren

|||

Thank you very much. Something I overlooked. I over analyzed the problem and almost created too much work for myself then what was needed. Now I have 12 reports to do a 2 min fix to each. Thanks again!!!

Is this DELETE possible?

I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.

DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)

Error msg: The table 'TableA' is ambiguous.

Can this be done with SQL or should I use T-SQL with cursors here?Try EXISTS or IN

DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_2 = y.col_1)

Or did you want:

DELETE TableA
WHERE EXISTS (SELECT * FROM TableA y
WHERE TableA.col_1 = y.col_2)

As always, I recommend you test with SELECT queries first. (No warrantees
implied, etc...)

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407131933.72eea682@.posting.google.c om...
I am getting error messages when I try to delete from a table using
the values in the table itself. The intent is to delete all rows from
TableA where col_2 matches any of the col_1 values.

DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
y.col_2)

Error msg: The table 'TableA' is ambiguous.

Can this be done with SQL or should I use T-SQL with cursors here?|||Try:

DELETE FROM x
FROM TableA x
INNER JOIN TableA y ON (x.col_1 = y.col_2)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407131933.72eea682@.posting.google.c om...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?|||Aaron,

Thanks for the tip. It worked!

On a related note, I am facing the same error when I try to update
TableA with data from the same TableA.

The command I used is this:

UPDATE TableA SET col_2 = y.col_2
FROM TableA x INNER JOIN TableA y
ON (x.col_1 = y.col_1)

Error message is: The table 'TableA' is ambiguous.

Do you have a similar solution?

"Aaron W. West" <tallpeak@.hotmail.NO.SPAM> wrote in message news:<P4udnTGzyaWGIWndRVn-hA@.speakeasy.net>...
> Try EXISTS or IN
> DELETE TableA
> WHERE EXISTS (SELECT * FROM TableA y
> WHERE TableA.col_2 = y.col_1)
> As always, I recommend you test with SELECT queries first. (No warrantees
> implied, etc...)|||On 14 Jul 2004 07:59:43 -0700, php newbie wrote:

> Aaron,
> Thanks for the tip. It worked!
> On a related note, I am facing the same error when I try to update
> TableA with data from the same TableA.
> The command I used is this:
> UPDATE TableA SET col_2 = y.col_2
> FROM TableA x INNER JOIN TableA y
> ON (x.col_1 = y.col_1)
> Error message is: The table 'TableA' is ambiguous.
> Do you have a similar solution?

If you're using "x" as a label for TableA, you need to use it throughout.

UPDATE x SET x.col_2 = y.col_2
FROM TableA x
INNER JOIN TableA y
ON (x.col_1 = y.col_1)|||I also got it to work like this:

CREATE TABLE #T (A int,B int)
INSERT #T SELECT 1,2
INSERT #T SELECT 2,2
INSERT #T SELECT 3,3
INSERT #T SELECT 4,3
INSERT #T SELECT 5,3
INSERT #T SELECT 6,4
INSERT #T SELECT 7,4

SELECT * FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)

DELETE FROM #T WHERE A IN (SELECT DISTINCT B FROM #T)|||>> On a related note, I am facing the same error when I try to update
TableA with data from the same TableA. <<

Why in the world do you think that SQL has a FROM clause in UPDATE and
DELETE? You are writing unpredictable, proprietary code that EVEN
IF IT WAS ALLOWED, would not produce results.

There is no FROM clause in a Standard SQL UPDATE statement; it would
make no sense. Other products (SQL Server, Sybase and Ingres) also
use the UPDATE .. FROM syntax, but with different semantics. So it
does not port, or even worse, when you do move it, it trashes your
database. Other programmers cannot read it and maintaining it is
harder. And when Microsoft decides to change it, you will have to do
a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
conversions? The last time the UPDATE FROM changed?

The correct syntax for a searched update statement is

<update statement> ::=
UPDATE <table name>
SET <set clause list>
[WHERE <search condition>]

<set clause list> ::=
<set clause> [{ , <set clause> }...]

<set clause> ::= <object column> = <update source
<update source> ::= <value expression> | NULL | DEFAULT

<object column> ::= <column name
The UPDATE clause simply gives the name of the base table or updatable
view to be changed.

Notice that no correlation name is allowed in the UPDATE clause; this
is to avoid some self-referencing problems that could occur. But it
also follows the data model in Standard SQL. When you give a table
expression a correlation name, it is to act as if a materialized table
with that correlation name has been created in the database. That
table then is dropped at the end of the statement. If you allowed
correlation names in the UPDATE clause, you would be updating the
materialized table, which would then disappear and leave the base
table untouched.

The SET clause is a list of columns to be changed or made; the WHERE
clause tells the statement which rows to use. For this discussion, we
will assume the user doing the update has applicable UPDATE privileges
for each <object column>.

* The WHERE Clause

As mentioned, the most important thing to remember about the WHERE
clause is that it is optional. If there is no WHERE clause, all rows
in the table are changed. This is a common error; if you make it,
immediately execute a ROLLBACK statement.

All rows that test TRUE for the <search condition> are marked as a
subset and not as individual rows. It is also possible that this
subset will be empty. This subset is used to construct a new set of
rows that will be inserted into the table when the subset is deleted
from the table. Note that the empty subset is a valid update that
will fire declarative referential actions and triggers.

* The SET Clause

Each assignment in the <set clause list> is executed in parallel and
each SET clause changes all the qualified rows at once. Or at least
that is the theoretical model. In practice, implementations will
first mark all of the qualified rows in the table in one pass, using
the WHERE clause. If there were no problems, then the SQL engine
makes a copy of each marked row in working storage. Each SET clause
is executed based on the old row image and the results are put in the
new row image. Finally, the old rows are deleted and the new rows are
inserted. If an error occurs during all of this, then system does a
ROLLBACK, the table is left unchanged and the errors are reported.
This parallelism is not like what you find in a traditional
third-generation programming language, so it may be hard to learn.
This feature lets you write a statement that will swap the values in
two columns, thus:

UPDATE MyTable
SET a = b, b = a;

This is not the same thing as

BEGIN ATOMIC
UPDATE MyTable
SET a = b;
UPDATE MyTable
SET b = a;
END;

In the first UPDATE, columns a and b will swap values in each row. In
the second pair of UPDATEs, column a will get all of the values of
column b in each row. In the second UPDATE of the pair, a, which now
has the same value as the original value of b, will be written back
into column b -- no change at all. There are some limits as to what
the value expression can be. The same column cannot appear more than
once in a <set clause list> -- which makes sense, given the parallel
nature of the statement. Since both go into effect at the same time,
you would not know which SET clause to use.

If a subquery expression is used in a <set clause>, and it returns a
single value, the result set is cast to a scalar; if it returns an
empty, the result set is cast to a NULL; if it returns multiple rows,
a cardinality violation is raised.

Same logic for the basic delete statement:

DELETE FROM Foobar
WHERE EXISTS
(SELECT *
FROM Foobar AS F1
WHERE Foobar.col_1 = F1.col_2)|||Dan,

This works beautifully and also applies directly to the update query. Thanks a lot!

My gratitudes also go to Russ and Jim. I appreciate your help.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<kZ9Jc.2879$Qu5.1593@.newsread2.news.pas.earthlink.n et>...
> Try:
> DELETE FROM x
> FROM TableA x
> INNER JOIN TableA y ON (x.col_1 = y.col_2)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||newtophp2000@.yahoo.com (php newbie) wrote in message news:<124f428e.0407131933.72eea682@.posting.google.com>...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?

Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)

With best regards!|||newtophp2000@.yahoo.com (php newbie) wrote in message news:<124f428e.0407131933.72eea682@.posting.google.com>...
> I am getting error messages when I try to delete from a table using
> the values in the table itself. The intent is to delete all rows from
> TableA where col_2 matches any of the col_1 values.
> DELETE FROM TableA FROM TableA x INNER JOIN TableA y ON (x.col_1 =
> y.col_2)
> Error msg: The table 'TableA' is ambiguous.
> Can this be done with SQL or should I use T-SQL with cursors here?

Hi,
try this:
DELETE x FROM TableA AS x INNER JOIN TableA AS y ON
(x.col_1 = y.col_2)

With best regards!|||> Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> conversions?

Joe,

Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the ANSI
standard? If so, why do you think major database vendors such as Microsoft,
Sybase and Oracle choose proprietary syntax?

--
Hope this helps.

Dan Guzman
SQL Server MVP

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> >> On a related note, I am facing the same error when I try to update
> TableA with data from the same TableA. <<
> Why in the world do you think that SQL has a FROM clause in UPDATE and
> DELETE? You are writing unpredictable, proprietary code that EVEN
> IF IT WAS ALLOWED, would not produce results.
> There is no FROM clause in a Standard SQL UPDATE statement; it would
> make no sense. Other products (SQL Server, Sybase and Ingres) also
> use the UPDATE .. FROM syntax, but with different semantics. So it
> does not port, or even worse, when you do move it, it trashes your
> database. Other programmers cannot read it and maintaining it is
> harder. And when Microsoft decides to change it, you will have to do
> a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> conversions? The last time the UPDATE FROM changed?
> The correct syntax for a searched update statement is
> <update statement> ::=
> UPDATE <table name>
> SET <set clause list>
> [WHERE <search condition>]
> <set clause list> ::=
> <set clause> [{ , <set clause> }...]
> <set clause> ::= <object column> = <update source>
> <update source> ::= <value expression> | NULL | DEFAULT
> <object column> ::= <column name>
> The UPDATE clause simply gives the name of the base table or updatable
> view to be changed.
> Notice that no correlation name is allowed in the UPDATE clause; this
> is to avoid some self-referencing problems that could occur. But it
> also follows the data model in Standard SQL. When you give a table
> expression a correlation name, it is to act as if a materialized table
> with that correlation name has been created in the database. That
> table then is dropped at the end of the statement. If you allowed
> correlation names in the UPDATE clause, you would be updating the
> materialized table, which would then disappear and leave the base
> table untouched.
> The SET clause is a list of columns to be changed or made; the WHERE
> clause tells the statement which rows to use. For this discussion, we
> will assume the user doing the update has applicable UPDATE privileges
> for each <object column>.
> * The WHERE Clause
> As mentioned, the most important thing to remember about the WHERE
> clause is that it is optional. If there is no WHERE clause, all rows
> in the table are changed. This is a common error; if you make it,
> immediately execute a ROLLBACK statement.
> All rows that test TRUE for the <search condition> are marked as a
> subset and not as individual rows. It is also possible that this
> subset will be empty. This subset is used to construct a new set of
> rows that will be inserted into the table when the subset is deleted
> from the table. Note that the empty subset is a valid update that
> will fire declarative referential actions and triggers.
> * The SET Clause
> Each assignment in the <set clause list> is executed in parallel and
> each SET clause changes all the qualified rows at once. Or at least
> that is the theoretical model. In practice, implementations will
> first mark all of the qualified rows in the table in one pass, using
> the WHERE clause. If there were no problems, then the SQL engine
> makes a copy of each marked row in working storage. Each SET clause
> is executed based on the old row image and the results are put in the
> new row image. Finally, the old rows are deleted and the new rows are
> inserted. If an error occurs during all of this, then system does a
> ROLLBACK, the table is left unchanged and the errors are reported.
> This parallelism is not like what you find in a traditional
> third-generation programming language, so it may be hard to learn.
> This feature lets you write a statement that will swap the values in
> two columns, thus:
> UPDATE MyTable
> SET a = b, b = a;
> This is not the same thing as
> BEGIN ATOMIC
> UPDATE MyTable
> SET a = b;
> UPDATE MyTable
> SET b = a;
> END;
> In the first UPDATE, columns a and b will swap values in each row. In
> the second pair of UPDATEs, column a will get all of the values of
> column b in each row. In the second UPDATE of the pair, a, which now
> has the same value as the original value of b, will be written back
> into column b -- no change at all. There are some limits as to what
> the value expression can be. The same column cannot appear more than
> once in a <set clause list> -- which makes sense, given the parallel
> nature of the statement. Since both go into effect at the same time,
> you would not know which SET clause to use.
> If a subquery expression is used in a <set clause>, and it returns a
> single value, the result set is cast to a scalar; if it returns an
> empty, the result set is cast to a NULL; if it returns multiple rows,
> a cardinality violation is raised.
> Same logic for the basic delete statement:
> DELETE FROM Foobar
> WHERE EXISTS
> (SELECT *
> FROM Foobar AS F1
> WHERE Foobar.col_1 = F1.col_2)|||Glad it helped.

--
Dan Guzman
SQL Server MVP

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407141856.659b70a8@.posting.google.c om...
> Dan,
> This works beautifully and also applies directly to the update query.
Thanks a lot!
> My gratitudes also go to Russ and Jim. I appreciate your help.|||Dan,

Both implementations pre-dated the '92 standard.

VC

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%vHJc.4441$Qu5.433@.newsread2.news.pas.earthli nk.net...
> > Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > conversions?
> Joe,
> Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the
ANSI
> standard? If so, why do you think major database vendors such as
Microsoft,
> Sybase and Oracle choose proprietary syntax?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> > >> On a related note, I am facing the same error when I try to update
> > TableA with data from the same TableA. <<
> > Why in the world do you think that SQL has a FROM clause in UPDATE and
> > DELETE? You are writing unpredictable, proprietary code that EVEN
> > IF IT WAS ALLOWED, would not produce results.
> > There is no FROM clause in a Standard SQL UPDATE statement; it would
> > make no sense. Other products (SQL Server, Sybase and Ingres) also
> > use the UPDATE .. FROM syntax, but with different semantics. So it
> > does not port, or even worse, when you do move it, it trashes your
> > database. Other programmers cannot read it and maintaining it is
> > harder. And when Microsoft decides to change it, you will have to do
> > a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > conversions? The last time the UPDATE FROM changed?
> > The correct syntax for a searched update statement is
> > <update statement> ::=
> > UPDATE <table name>
> > SET <set clause list>
> > [WHERE <search condition>]
> > <set clause list> ::=
> > <set clause> [{ , <set clause> }...]
> > <set clause> ::= <object column> = <update source>
> > <update source> ::= <value expression> | NULL | DEFAULT
> > <object column> ::= <column name>
> > The UPDATE clause simply gives the name of the base table or updatable
> > view to be changed.
> > Notice that no correlation name is allowed in the UPDATE clause; this
> > is to avoid some self-referencing problems that could occur. But it
> > also follows the data model in Standard SQL. When you give a table
> > expression a correlation name, it is to act as if a materialized table
> > with that correlation name has been created in the database. That
> > table then is dropped at the end of the statement. If you allowed
> > correlation names in the UPDATE clause, you would be updating the
> > materialized table, which would then disappear and leave the base
> > table untouched.
> > The SET clause is a list of columns to be changed or made; the WHERE
> > clause tells the statement which rows to use. For this discussion, we
> > will assume the user doing the update has applicable UPDATE privileges
> > for each <object column>.
> > * The WHERE Clause
> > As mentioned, the most important thing to remember about the WHERE
> > clause is that it is optional. If there is no WHERE clause, all rows
> > in the table are changed. This is a common error; if you make it,
> > immediately execute a ROLLBACK statement.
> > All rows that test TRUE for the <search condition> are marked as a
> > subset and not as individual rows. It is also possible that this
> > subset will be empty. This subset is used to construct a new set of
> > rows that will be inserted into the table when the subset is deleted
> > from the table. Note that the empty subset is a valid update that
> > will fire declarative referential actions and triggers.
> > * The SET Clause
> > Each assignment in the <set clause list> is executed in parallel and
> > each SET clause changes all the qualified rows at once. Or at least
> > that is the theoretical model. In practice, implementations will
> > first mark all of the qualified rows in the table in one pass, using
> > the WHERE clause. If there were no problems, then the SQL engine
> > makes a copy of each marked row in working storage. Each SET clause
> > is executed based on the old row image and the results are put in the
> > new row image. Finally, the old rows are deleted and the new rows are
> > inserted. If an error occurs during all of this, then system does a
> > ROLLBACK, the table is left unchanged and the errors are reported.
> > This parallelism is not like what you find in a traditional
> > third-generation programming language, so it may be hard to learn.
> > This feature lets you write a statement that will swap the values in
> > two columns, thus:
> > UPDATE MyTable
> > SET a = b, b = a;
> > This is not the same thing as
> > BEGIN ATOMIC
> > UPDATE MyTable
> > SET a = b;
> > UPDATE MyTable
> > SET b = a;
> > END;
> > In the first UPDATE, columns a and b will swap values in each row. In
> > the second pair of UPDATEs, column a will get all of the values of
> > column b in each row. In the second UPDATE of the pair, a, which now
> > has the same value as the original value of b, will be written back
> > into column b -- no change at all. There are some limits as to what
> > the value expression can be. The same column cannot appear more than
> > once in a <set clause list> -- which makes sense, given the parallel
> > nature of the statement. Since both go into effect at the same time,
> > you would not know which SET clause to use.
> > If a subquery expression is used in a <set clause>, and it returns a
> > single value, the result set is cast to a scalar; if it returns an
> > empty, the result set is cast to a NULL; if it returns multiple rows,
> > a cardinality violation is raised.
> > Same logic for the basic delete statement:
> > DELETE FROM Foobar
> > WHERE EXISTS
> > (SELECT *
> > FROM Foobar AS F1
> > WHERE Foobar.col_1 = F1.col_2)|||> Both implementations pre-dated the '92 standard.

So it appears many vendors implemented proprietary SQL extensions to address
deficiencies in the SQL-89 standard. Once the standard was enhanced to
address the need, many vendors added support for ANSI-style joins as well.

Portability is a consideration but, IMHO, is less important than
functionality in most environments.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"VC" <boston103@.hotmail.com> wrote in message
news:LuPJc.87240$JR4.26140@.attbi_s54...
> Dan,
> Both implementations pre-dated the '92 standard.
> VC
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%vHJc.4441$Qu5.433@.newsread2.news.pas.earthli nk.net...
> > > Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > > conversions?
> > Joe,
> > Just out of curiosity, was LEFT OUTER JOIN (et. al.) always part of the
> ANSI
> > standard? If so, why do you think major database vendors such as
> Microsoft,
> > Sybase and Oracle choose proprietary syntax?
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> > news:18c7b3c2.0407141855.550aba73@.posting.google.c om...
> > > >> On a related note, I am facing the same error when I try to update
> > > TableA with data from the same TableA. <<
> > > > Why in the world do you think that SQL has a FROM clause in UPDATE and
> > > DELETE? You are writing unpredictable, proprietary code that EVEN
> > > IF IT WAS ALLOWED, would not produce results.
> > > > There is no FROM clause in a Standard SQL UPDATE statement; it would
> > > make no sense. Other products (SQL Server, Sybase and Ingres) also
> > > use the UPDATE .. FROM syntax, but with different semantics. So it
> > > does not port, or even worse, when you do move it, it trashes your
> > > database. Other programmers cannot read it and maintaining it is
> > > harder. And when Microsoft decides to change it, you will have to do
> > > a re-write. Remember the deprecated "*=" versus "LEFT OUTER JOIN"
> > > conversions? The last time the UPDATE FROM changed?
> > > > The correct syntax for a searched update statement is
> > > > <update statement> ::=
> > > UPDATE <table name>
> > > SET <set clause list>
> > > [WHERE <search condition>]
> > > > <set clause list> ::=
> > > <set clause> [{ , <set clause> }...]
> > > > <set clause> ::= <object column> = <update source>
> > > > <update source> ::= <value expression> | NULL | DEFAULT
> > > > <object column> ::= <column name>
> > > > The UPDATE clause simply gives the name of the base table or updatable
> > > view to be changed.
> > > > Notice that no correlation name is allowed in the UPDATE clause; this
> > > is to avoid some self-referencing problems that could occur. But it
> > > also follows the data model in Standard SQL. When you give a table
> > > expression a correlation name, it is to act as if a materialized table
> > > with that correlation name has been created in the database. That
> > > table then is dropped at the end of the statement. If you allowed
> > > correlation names in the UPDATE clause, you would be updating the
> > > materialized table, which would then disappear and leave the base
> > > table untouched.
> > > > The SET clause is a list of columns to be changed or made; the WHERE
> > > clause tells the statement which rows to use. For this discussion, we
> > > will assume the user doing the update has applicable UPDATE privileges
> > > for each <object column>.
> > > > * The WHERE Clause
> > > > As mentioned, the most important thing to remember about the WHERE
> > > clause is that it is optional. If there is no WHERE clause, all rows
> > > in the table are changed. This is a common error; if you make it,
> > > immediately execute a ROLLBACK statement.
> > > > All rows that test TRUE for the <search condition> are marked as a
> > > subset and not as individual rows. It is also possible that this
> > > subset will be empty. This subset is used to construct a new set of
> > > rows that will be inserted into the table when the subset is deleted
> > > from the table. Note that the empty subset is a valid update that
> > > will fire declarative referential actions and triggers.
> > > > * The SET Clause
> > > > Each assignment in the <set clause list> is executed in parallel and
> > > each SET clause changes all the qualified rows at once. Or at least
> > > that is the theoretical model. In practice, implementations will
> > > first mark all of the qualified rows in the table in one pass, using
> > > the WHERE clause. If there were no problems, then the SQL engine
> > > makes a copy of each marked row in working storage. Each SET clause
> > > is executed based on the old row image and the results are put in the
> > > new row image. Finally, the old rows are deleted and the new rows are
> > > inserted. If an error occurs during all of this, then system does a
> > > ROLLBACK, the table is left unchanged and the errors are reported.
> > > This parallelism is not like what you find in a traditional
> > > third-generation programming language, so it may be hard to learn.
> > > This feature lets you write a statement that will swap the values in
> > > two columns, thus:
> > > > UPDATE MyTable
> > > SET a = b, b = a;
> > > > This is not the same thing as
> > > > BEGIN ATOMIC
> > > UPDATE MyTable
> > > SET a = b;
> > > UPDATE MyTable
> > > SET b = a;
> > > END;
> > > > In the first UPDATE, columns a and b will swap values in each row. In
> > > the second pair of UPDATEs, column a will get all of the values of
> > > column b in each row. In the second UPDATE of the pair, a, which now
> > > has the same value as the original value of b, will be written back
> > > into column b -- no change at all. There are some limits as to what
> > > the value expression can be. The same column cannot appear more than
> > > once in a <set clause list> -- which makes sense, given the parallel
> > > nature of the statement. Since both go into effect at the same time,
> > > you would not know which SET clause to use.
> > > > If a subquery expression is used in a <set clause>, and it returns a
> > > single value, the result set is cast to a scalar; if it returns an
> > > empty, the result set is cast to a NULL; if it returns multiple rows,
> > > a cardinality violation is raised.
> > > > Same logic for the basic delete statement:
> > > > DELETE FROM Foobar
> > > WHERE EXISTS
> > > (SELECT *
> > > FROM Foobar AS F1
> > > WHERE Foobar.col_1 = F1.col_2)|||--CELKO-- (jcelko212@.earthlink.net) writes:
> There is no FROM clause in a Standard SQL UPDATE statement; it would
> make no sense.

Of course it would.

> Other programmers cannot read it and maintaining it is harder.

Au contraire, I find nested subselects more difficult to understand
and maintain.

So you have this UPDATE statement:

UPDATE tblA
SET col1 = z.col1
col2 = y.col2
FROM tblA a
JOIN ...
WHERE ...

And we are interested in which rows this statement actually hits. A little
cut and paste, select "UPDATE tblA SET", type SELECT, press Execute et
voil!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Is this correct use of INSTEAD OF Triggers?

I am loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. When the process is
finished, I'll have only good rows in B, and exeption rows in C.

I am investigating INSTEAD OF triggers, however my question to the
group is, is there a better or best practice for this scenario? This
must be common. Any high-level tips or direction will be highly
appreciated.

DAP>> loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. <<

You might want to use a cursor that attempts to insert each A row into
B and throws the exceptions into C. This would give you better control
and perhaps a chance to fix the bad rows with (ugh!) procedural code.

A moire set-oriented approach woudl be to create a VIEW on A whch has
the B constraints:

CREATE VIEW GoodA
AS SELECT *
FROM A
WHERE << B's constraints as predicates>> ;

You are probably thinking that the next step is to use:

CREATE VIEW BadA
AS SELECT *
FROM A
WHERE NOT (<< B's constraints as predicates>>);

But this does not work. A CHECK() constraint will accept an UNKNOWN
result from its predicate; a WHERE clause will reject them. You will
have to write a little extra code in each predicate to handle NULLs.

example:

CREATE TABLE B
( ..
foo INTEGER CHECK ( foo >= 0), -- works for null
..);

SELECT *
FROM A
WHERE ( foo >= 0 OR foo IS NULL);|||Hi

You can try something like:

INSERT INTO Table C
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINT FAIL
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINTS PASS
OR
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)

John

"Dan" <dpratte@.dpratte.com> wrote in message
news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common. Any high-level tips or direction will be highly
> appreciated.
> DAP|||Dan (dpratte@.dpratte.com) writes:
> I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common.

Not really.

I think the only way to do this without duplicating the constraints is
run a cursor one-by-one as suggested by Celko. An improvement could be
to first attempt to insert all, and if there is an error, use the
cursor as a fallback. But you could not do this in an INSTEAD OF
trigger, because an error in a trigger aborts the batch. You see,
the whole idea is that the INSERT statement should be atomic, either
all rows make it, or others not.

An alternative would be move the constraints to the trigger and check
for them there. An INSTEAD OF trigger would then redo the original
INSERT statement for the good rows. An AFTER trigger would delete
the bad rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

I missed the title to this! Rather than use a trigger I would put the logic
into a stored procedure.

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:427d0150$0$1878$da0feed9@.news.zen.co.uk...
> Hi
> You can try something like:
> INSERT INTO Table C
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINT FAIL>
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINTS PASS>
> OR
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)
> John
> "Dan" <dpratte@.dpratte.com> wrote in message
> news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>>I am loading data from table A into table B. Certain columns in B have
>> check constraints. I'd like for any rows from A, which violate
>> constraints, to be inserted into a third table, C. When the process is
>> finished, I'll have only good rows in B, and exeption rows in C.
>>
>> I am investigating INSTEAD OF triggers, however my question to the
>> group is, is there a better or best practice for this scenario? This
>> must be common. Any high-level tips or direction will be highly
>> appreciated.
>>
>> DAP
>>

Is there performance penalty for returning resultsets instead of rows

> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
I wasn't sure about the overhead so created the following procs.
CREATE PROC Proc1 AS
SELECT * FROM
(SELECT 1 AS Test
UNION ALL SELECT 2
UNION ALL SELECT 3
<snip>
UNION ALL SELECT 40) As Test
GO
CREATE PROC Proc2 AS
SELECT 1
SELECT 2
SELECT 3
<snip>
SELECT 40
GO
I then ran a test of 10,000 iterations with the code below. The app and SQL
were on the same machine:
private void AdHocTest()
{
_connection.Open();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Connection = _connection;
command.CommandText = "dbo.Proc1";
RunTest(connection, command, 10000);
command.CommandText = "dbo.Proc2";
RunTest(connection, command, 10000);
_connection.Close();
}
private void RunTest(SqlConnection connection,
SqlCommand command,
int iterations)
{
DateTime startTime = DateTime.Now;
for (int i = 0; i < iterations; ++i)
{
//execute command and consume all results
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read()) ;
}
while (reader.NextResult());
reader.Close();
}
System.Diagnostics.Trace.WriteLine(
string.Format("Test {0} duration is {1}",
command.CommandText,
DateTime.Now.Subtract(startTime).ToString()));
}
The single result method was 1 second for all 10,000 iterations and the
multiple result method was about 2 seconds. I would expect an even more
pronounced difference with SQL on a separate box.
Of course, this test didn't include the overhead of the server cursor or
client processing. In your actual application, the performance difference
probably won't matter unless you have a lot of users.
Note that you might run into issues with a single 'generic' paging solution.
There are many different pagination techniques and no single one is best for
all situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:OKkF9y3aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> I'm using server cursor for generic paging.
> Interesting part is this:
> FETCH RELATIVE @.StartRow FROM cur
> WHILE @.PageSize > 1 AND @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM cur
> SET @.PageSize = @.PageSize - 1
> END
> Instead of single result set with N rows, this returns N result sets with
> 1 row. I can handle that in my client, that's not an issue.
> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
> Thanks,
> Dejan> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
If a count of the number of rows is not needed for your paging technique,
you can still provide the user with separate 'get count' button. We used
that technique in one of our apps because we had tables with hundreds of
millions of rows and querying was ad-hoc. Users could still navigate with
'next' and 'prev' button as well as jump to specific pages.
Note that SELECT COUNT(*) will use the narrowest useful index so index
tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
using a cursor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
> Thanks Dan for these numbers. In the mean time I performed some tests of
> my own. To my surprise, I found out that main problem is not with cursors
> and result sets, but with SELECT COUNT(*)!
> For 50k rows and when page is at the beginning of table COUNT takes about
> 10x more time than fetching 30 full rows!
> I need that count, to display exact number of pages to user.
> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
> Dejan|||I forgot to add that you can get an accurate table rowcount in SQL Server
2005 with:
SELECT rowcnt
FROM sysindexes
WHERE id = OBJECT_ID('dbo.MyTable)
AND indid IN(0,1)
The returned value is an approximation in SQL 2000 and might not be
accurate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23874LaHbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> If a count of the number of rows is not needed for your paging technique,
> you can still provide the user with separate 'get count' button. We used
> that technique in one of our apps because we had tables with hundreds of
> millions of rows and querying was ad-hoc. Users could still navigate with
> 'next' and 'prev' button as well as jump to specific pages.
> Note that SELECT COUNT(*) will use the narrowest useful index so index
> tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
> using a cursor.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
> news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
>

Is there information_schema.view for count of rows in tables?

Hello,
Is there some kind of information_schema.something which lists tables and
the current count of rows in each table?
Thanks,
RichI tried something like this which did not work:
select table_name, (Select count(*) from table_name) as NumOfRows
from information_schema.tables
Any suggestions?
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||See
http://www.databasejournal.com/feat...cle.php/3441031
The above details two methods of getting the results that you want; one by
using a cursor, and another by using an undocumented stored procedure in SQL
Server 2000. Since I don't know what version you are using, I would recommen
d
looking at the cursor method.
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||Rich (Rich@.discussions.microsoft.com) writes:
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
Not in INFORMATION_SCHEMA, but in the system table sysindexes:
SELECT object_name(id), rows
FROM sysindexes
WHERE indid IN (0, 1)
I should add that the value you see here may not be exactly on the
mark, but most often it's close enough. This is a lot faster than
accessing each table.
The WHERE clause looks funny, but sysindexes is a bit special. For
each table there is always a row with indid = 0 *or* 1 - never both.
indid is one if the table has a clustered index, else it's zero.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As a follow up, you can use
SELECT object_name(i.[id]), rows
FROM sysindexes i INNER JOIN sysobjects o
ON i.[id] = o.[id]
WHERE i.indid IN (0, 1) AND o.xtype = 'U'
if you just want the row count of user-defined base tables. You can run the
following to get a comparison between counting the rows with COUNT(*) and
what appears in sysindexes. In a DB that I have, there was no difference for
any of the tables.
set nocount on
declare @.cnt int
declare @.table varchar(128)
declare @.cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
where table_type = 'base table'
open tables
fetch next from tables into @.table
while @.@.fetch_status = 0
begin
set @.cmd = 'select ''' + @.table + ''', count(*) from ' + @.table
insert into #rowcount exec (@.cmd)
fetch next from tables into @.table
end
CLOSE tables
DEALLOCATE tables
SELECT t1.tablename, t1.rowcnt AS "Counted with COUNT(*)", t2.[rows] AS
"Counted via sysindexes"
FROM #rowcount t1 INNER JOIN (
SELECT object_name(i.[id]) AS "tablename", i.[rows]
FROM sysindexes i INNER JOIN sysobjects o
ON i.[id] = o.[id]
WHERE i.indid IN (0, 1) AND o.xtype = 'U') t2
ON t1.tablename = t2.tablename
drop table #rowcount
--
"Erland Sommarskog" wrote:

> Rich (Rich@.discussions.microsoft.com) writes:
> Not in INFORMATION_SCHEMA, but in the system table sysindexes:
> SELECT object_name(id), rows
> FROM sysindexes
> WHERE indid IN (0, 1)
> I should add that the value you see here may not be exactly on the
> mark, but most often it's close enough. This is a lot faster than
> accessing each table.
> The WHERE clause looks funny, but sysindexes is a bit special. For
> each table there is always a row with indid = 0 *or* 1 - never both.
> indid is one if the table has a clustered index, else it's zero.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Thank you all for your replies. I did try both methods, the sysIndex table
method, and the cursor method (which included the sysindex table).
A few of the tables did have a slightly different count with count(*) than
the count in sysindex. I will guess that count(*) was probably a little bit
more current, as these are live tables getting data as we speak.
Does anyone know if peformance on data entry was affected while I ran the
cursor? The cursor took 25 seconds to run. Or is running this kind of
cursor pretty seamless against the data entry table?
Thanks again,
Rich
"Rich" wrote:

> Hello,
> Is there some kind of information_schema.something which lists tables and
> the current count of rows in each table?
> Thanks,
> Rich|||> I will guess that count(*) was probably a little bit more current
The count(*) rows (assuming you didn't use a nolock hint) will be almost
perfect as of the instant that the query finishes. The sysindexes value is
a reasonably recent value that is used for the optimizer to make "guesses"
as to how many rows are in the table much much faster than counting all of
the rows.

> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
Performance is affected no matter what the query :) Seriously, it is
affected in that some minor blocking will take place, and if you don't have
an index on the table, every row will have to be "touched" and locked during
the counting process. But the overhead on any other queries is likely
minimal.
The key here is to base which method you use based on your needs. If you
just want to know the number of rows in the table, then probably using
sysindexes is best, unless you need perfect results. Otherwise, locking is
fine. Note I said almost perfect in the first paragraph. Under the default
conditions in SQL Server, users can delete or insert rows that have already
been counted, so it is only as perfect as the users of the data allow.
I could keep going, but unless you really care about being perfect, it is
not really worth it :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:7C3F3BE6-296E-4F88-9C22-AB15DAA66050@.microsoft.com...
> Thank you all for your replies. I did try both methods, the sysIndex
> table
> method, and the cursor method (which included the sysindex table).
> A few of the tables did have a slightly different count with count(*) than
> the count in sysindex. I will guess that count(*) was probably a little
> bit
> more current, as these are live tables getting data as we speak.
> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
> Thanks again,
> Rich
> "Rich" wrote:
>|||Rich (Rich@.discussions.microsoft.com) writes:
> Does anyone know if peformance on data entry was affected while I ran the
> cursor? The cursor took 25 seconds to run. Or is running this kind of
> cursor pretty seamless against the data entry table?
Unless there was a NOLOCK on the SELECT COUNT(*) queries, users could
experience blocking when you run the query. This can be particularly
noticeable on a large table that does not have any non-clustered indexes.
(If there is a non-clustered index, the COUNT(*) will run over that
index, which is cheaper than scanning the entire table.)
The query on sysindex is considerably leaner on resources, and it's
unlikely that it would cause blocking.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Is there anything like rowid, rownum like in MySql and Oracle?

Hi,

i am new to SQL Server. I want to write a query where in i want to delete duplicate rows from a table keeping the master copy.

If it is MySQL or Oracle we can write that using built in rownum or rowid. How to do that task in SQL Server 2005. Is there anything like rowid, rownum in SQL Server? If not suggest me a way to do that?

...aazad

not exactally , but u can make use of 'TOP' or row_number() function..

select top 1 from table 1 order by column1

u can use top intelligently to get top/bottom nth row... given u have somethin to orderby

|||Rownum is a psuedo column that generates a logical sequence number so it will change depending on the query execution plan, data etc. Rowid on the other hand is a physical identifier (at least in Oracle). So how are you using these in your queries? What is the purpose of using something like ROWID? You do have primary key or unique key constraints on your tables right! It will be easier to suggest the alternatives if we know your use cases.|||

Hi chandar,

There can be a senario where a table has no primary key and has data. Later when i want to make a column as primary key, i need to delete the duplicates, which i dont want to do it manually. so i shud write a query where i can delete duplicate rows keeping one copy of it. I worked with MySQL and in MySQL i can write a query as follows

delete from test where rowid in ( select rownum from test where rownum not in ( select min(rownum) from test group by all_columns having count(*) > 1 ) group by all_columns having count(*) > 1

The above code deletes the duplicates the master copy in MySQL. I am using that logical column rownum. How to do the same job in SQL Server 2005?

Regards..,

Aazad

|||

okk...lect us say u want to make column1 as ur primary key in table1 , so to find out the duplicate(or more) entries of this key , use the following query...

select column1 from table1

group by column1

having count(column1)>1

this will enlist all the entries for column1 which r repeating...

|||

Thank god .. you are using SQL Server 2005 use the following query

Example:

CREATE TABLE Table1

(

[Id] [int] NULL

)

go

INSERT INTO Table1 values(10);

INSERT INTO Table1 values(10);

INSERT INTO Table1 values(20);

INSERT INTO Table1 values(20);

go

With Test(rownum,ID)

as

(

Select Row_Number() OVER (ORDER BY ID), * From Table1

)

Delete From Test Where rownum in

(

Select A.rownum From Test A JOIN Test B On B.Id=A.ID and A.rownum >= B.rownum

Group bY A.rownum,A.ID Having Count(A.ID) <> 1

)

|||hi
use newid() function
good luck

Is there anything like DoEvents in a stored proc?

Hello,
I have a stored proc that does a series of things
1) create table if doesn't exist else delete all rows
2) select into this table
3) create another table if doesn't exist else delet all rows
4) select large number of records into this table
5) count records
6) delete records if more than a certain number of records
and I also print status in between each for debugging.
I am wondering if there is a way to have the stored procedure give up
some cpu time and also output the print statements before the entire
stored proc has finished.
Thanks
RandyIt seems that your procedure is written ineficiently...
prints are just data and get returned when you come to them.
Probbaly Waitfor Delay will do the job for you.
Bojidar Alexandrov
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>|||I've used PRINT statements for debugging large SPROCS - they do not come out
in the expected spot at all times.
I assume this is related to the way ADO from VISUAL BASIC has similar
issues - the PRINT statements go into the ERROR COLLECTION of the CONNECTION
OJBECT and seem to wait for RECORDSET processing - can't seem to get both.
In QUERY ANALYZER I've gotten around this issue by sometimes setting the
RESULT PANE to RETURN TEXT as opposed to DATA in a GRID - that way you get
one result window for both the RECORDSET data and the PRINT statements.
I've also at times changed all my PRINT statements to be SELECT statements
for debugging. And in reality if some of those "prints" remain in the
production code, it's better to have them as SELECT statements.
"Randy D" <no_freakin_spam@.sickofit.com> wrote in message
news:MPG.1b07f639b3d07b3b98968b@.news.supernews.com...
> Hello,
> I have a stored proc that does a series of things
> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
> and I also print status in between each for debugging.
>
> I am wondering if there is a way to have the stored procedure give up
> some cpu time and also output the print statements before the entire
> stored proc has finished.
>
> Thanks
> Randy
>|||> 1) create table if doesn't exist else delete all rows
> 2) select into this table
> 3) create another table if doesn't exist else delet all rows
> 4) select large number of records into this table
> 5) count records
> 6) delete records if more than a certain number of records
I would question whether it makes sense to move data around in this
inefficient sequential manner. Can you create a view rather than copying
rows into another table? Inserting rows then counting and deleting them
seems especially pointless. Why can't you write your query (4) to insert
only the rows you require to start with?
If you need more help, please post DDL, sample data and show your required
result.
http://www.aspfaq.com/5006
--
David Portas
SQL Server MVP
--|||Thanks for the reply.
The reason I can't just select the records I need in the first place is
because I have to make sure that I have complete months worth of data.
So my logic is something like this.
1) Select top 104000 records into temp table
2) Select count if I have exactly 104000 records then I know that most
likely I have a partial months worth of data so now select the oldest
date, do some date math so that I have the date of the first day of the
next month then delete all records that are older than that.
It is actually even more complicated than that because I am building up
very specific data that could not be retrieved in a single query.
I know that it is not efficient, but this runs at night and so it is
more important to have the data accurate and the stored proc easy to
debug.
Thanks
Randy
In article <epadnUGk2aiLNwPdRVn-uQ@.giganews.com>,
REMOVE_BEFORE_REPLYING_dportas@.acm.org says...
> > 1) create table if doesn't exist else delete all rows
> > 2) select into this table
> > 3) create another table if doesn't exist else delet all rows
> > 4) select large number of records into this table
> > 5) count records
> > 6) delete records if more than a certain number of records
> I would question whether it makes sense to move data around in this
> inefficient sequential manner. Can you create a view rather than copying
> rows into another table? Inserting rows then counting and deleting them
> seems especially pointless. Why can't you write your query (4) to insert
> only the rows you require to start with?
> If you need more help, please post DDL, sample data and show your required
> result.
> http://www.aspfaq.com/5006
>|||You want (at most) the top 104000 rows by date but only complete months? Try
this:
DECLARE @.dt DATETIME
SET ROWCOUNT 104001
SELECT @.dt = DATEADD(MONTH,DATEDIFF(MONTH,-1,xdate),0)
FROM SomeTable
ORDER BY xdate DESC
SET ROWCOUNT 0
SELECT *
FROM SomeTable
WHERE xdate >= @.dt
I'll be the first to admit that this is far from perfect. It uses a
proprietary, undocumented trick that isn't guaranteed to work in future
versions of SQLServer. I'm sure there are better methods of getting the
result you need but without a full understanding of your business
requirements this is just intended to illustrate one possibility.
--
David Portas
SQL Server MVP
--

Is there an easy way to switch a table from "ANSI NULLS OFF" to "ANSI NULLS OFF"

We have some tables with quite a lot of data (in the order of tens of
millions of rows) which were created with ANSI NULLS OFF. Now that we
want to create some indexed views we need these tables to have ANSI
NULLS ON.
We can create new temp tables, bulk copy the data over and recreate all
cosntraints and indices.
But is there an easier way?
Thanks,
AnilA table doesn't care about this setting. It is the connection that is working against this table
which need to have the correct setting.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<sqlpractitioner@.gmail.com> wrote in message
news:1160615076.764380.293820@.i3g2000cwc.googlegroups.com...
> We have some tables with quite a lot of data (in the order of tens of
> millions of rows) which were created with ANSI NULLS OFF. Now that we
> want to create some indexed views we need these tables to have ANSI
> NULLS ON.
> We can create new temp tables, bulk copy the data over and recreate all
> cosntraints and indices.
> But is there an easier way?
> Thanks,
> Anil
>|||Tibor Karaszi wrote:
> A table doesn't care about this setting. It is the connection that is working against this table
> which need to have the correct setting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
The setting at table-level does matter if you create computed columns
or if you need to create an indexed view.
Unfortunately, the only way I know of to change it is to recreate the
table.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
Indeed, I just tried with an index over a computed columns.
Thanks, David. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1160645954.605837.156410@.e3g2000cwe.googlegroups.com...
> Tibor Karaszi wrote:
>> A table doesn't care about this setting. It is the connection that is working against this table
>> which need to have the correct setting.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
> Unfortunately, the only way I know of to change it is to recreate the
> table.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||> The setting at table-level does matter if you create computed columns
> or if you need to create an indexed view.
> Unfortunately, the only way I know of to change it is to recreate the
> table.
>
Indexed views is the reason we are looking at this. Do you know if
there is an easier option in SQL 2005? We are on SQL 2000 now.
Thanks,
Anil|||<sqlpractitioner@.gmail.com> wrote in message
news:1160670946.200564.266370@.m73g2000cwd.googlegroups.com...
>> The setting at table-level does matter if you create computed columns
>> or if you need to create an indexed view.
>> Unfortunately, the only way I know of to change it is to recreate the
>> table.
> Indexed views is the reason we are looking at this. Do you know if
> there is an easier option in SQL 2005? We are on SQL 2000 now.
> Thanks,
> Anil
>
There is no change in 2005 that I know of. ANSI NULLS ON has been the
preferred option for so long, maybe supporting the OFF setting isn't a high
priority for MS. If you want to change that you could post a suggestion at:
http://connect.microsoft.com/SQLServer/feedback/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Oct 12, 5:35 pm, sqlpractitio...@.gmail.com wrote:
> > The setting at table-level does matter if you create computed columns
> > or if you need to create an indexed view.
> > Unfortunately, the only way I know of to change it is to recreate the
> > table.Indexed views is the reason we are looking at this. Do you know if
> there is an easier option in SQL 2005? We are on SQL 2000 now.
> Thanks,
> Anil
There is no change in 2005 that I know of. ANSI NULLS ON has been the
preferred option for so long, maybe supporting the OFF setting isn't a
high priority for MS. If you want to change that you could post a
suggestion at:
http://connect.microsoft.com/SQLServer/feedback/
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--