Hello. I update our SQL QA server about four times a day and back it up
before every update. There are three DBs that I have to backup. Each
backup is given a specific name accoring to the 'Update Name and Number".
This take a lot of time to go through Enterprise Manager every time.
For one backup session the the backup names would be:
DDMMYY_QA_UpdateNumber_DB1.bak
DDMMYY_QA_UpdateNumber_DB2.bak
DDMMYY_QA_UpdateNember_DB3.bak
I know how to backup the DBs with a dos batch, but I have to then change all
the names which is still a pain.
If I could have a batch file that I click on and it prompts me for:
DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283 and
hit enter, and it would produce:
092404_QA_Update283_DB1.bak
092404_QA_Update283_DB2.bak
092404_QA_Update283_DB3.bak
My life would change!
Is this possible? How could it be done?
Thanks in advance for any help!!!
Hi,
No need to write a batch program, Go with a small procedure which generates
the backup file names based on MMDDYYHHMinSS
See the below site for script and details:-
http://www.microsoft.com/india/msdn/articles/190.aspx
Note:
Change the script slightly to backup to local drives...
Thanks
Hari
MCDBA
"Tom" <none@.none.com> wrote in message
news:ewQtjdmoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hello. I update our SQL QA server about four times a day and back it up
> before every update. There are three DBs that I have to backup. Each
> backup is given a specific name accoring to the 'Update Name and Number".
> This take a lot of time to go through Enterprise Manager every time.
> For one backup session the the backup names would be:
> DDMMYY_QA_UpdateNumber_DB1.bak
> DDMMYY_QA_UpdateNumber_DB2.bak
> DDMMYY_QA_UpdateNember_DB3.bak
> I know how to backup the DBs with a dos batch, but I have to then change
> all
> the names which is still a pain.
> If I could have a batch file that I click on and it prompts me for:
> DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283
> and
> hit enter, and it would produce:
> 092404_QA_Update283_DB1.bak
> 092404_QA_Update283_DB2.bak
> 092404_QA_Update283_DB3.bak
> My life would change!
> Is this possible? How could it be done?
> Thanks in advance for any help!!!
>
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Thursday, March 29, 2012
Tuesday, March 20, 2012
Backgound update of FTI
I know that with background updates the FTI is updated incrementally as
inserts and updates which affect the indexed column occur. How can I tell if
an FTI on a column has been set for background updates?
Also, do you know if there is a performance downside to having background
updates enabled? Could you point me to any studies or papers where the pros
and cons of the different update strategies (background Vs Incremental Vs
Full) are explored?
If background updating is enabled, then from SQL Profiler how can I track
the queries being run for the updates and how long the updates are taking?
Thanks.
Murli
Some people prefer to schedule the updating of the indexing process to a
time when there is less activity on their system.
To tell if you are using update index in background issue a
select
objectproperty(object_id('authors'),'TableFullText BackgroundUpdateIndexOn')
where authors is the table you are interested in. A value of 1 will indicate
change tracking with update index in background.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Murli" <Murli@.discussions.microsoft.com> wrote in message
news:0F65C42D-CDED-4D74-9442-3045D98BAC56@.microsoft.com...
> I know that with background updates the FTI is updated incrementally as
> inserts and updates which affect the indexed column occur. How can I tell
if
> an FTI on a column has been set for background updates?
> Also, do you know if there is a performance downside to having background
> updates enabled? Could you point me to any studies or papers where the
pros
> and cons of the different update strategies (background Vs Incremental Vs
> Full) are explored?
> If background updating is enabled, then from SQL Profiler how can I track
> the queries being run for the updates and how long the updates are taking?
> Thanks.
> Murli
>
inserts and updates which affect the indexed column occur. How can I tell if
an FTI on a column has been set for background updates?
Also, do you know if there is a performance downside to having background
updates enabled? Could you point me to any studies or papers where the pros
and cons of the different update strategies (background Vs Incremental Vs
Full) are explored?
If background updating is enabled, then from SQL Profiler how can I track
the queries being run for the updates and how long the updates are taking?
Thanks.
Murli
Some people prefer to schedule the updating of the indexing process to a
time when there is less activity on their system.
To tell if you are using update index in background issue a
select
objectproperty(object_id('authors'),'TableFullText BackgroundUpdateIndexOn')
where authors is the table you are interested in. A value of 1 will indicate
change tracking with update index in background.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Murli" <Murli@.discussions.microsoft.com> wrote in message
news:0F65C42D-CDED-4D74-9442-3045D98BAC56@.microsoft.com...
> I know that with background updates the FTI is updated incrementally as
> inserts and updates which affect the indexed column occur. How can I tell
if
> an FTI on a column has been set for background updates?
> Also, do you know if there is a performance downside to having background
> updates enabled? Could you point me to any studies or papers where the
pros
> and cons of the different update strategies (background Vs Incremental Vs
> Full) are explored?
> If background updating is enabled, then from SQL Profiler how can I track
> the queries being run for the updates and how long the updates are taking?
> Thanks.
> Murli
>
Saturday, February 25, 2012
Avoiding temp tables
Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:
>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:
>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan
Friday, February 24, 2012
Avoid update without where clause
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
No, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald
|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sq...y_triggers.htm
but may not be completely safe.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you want to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened?". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>
>.
>
|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message[vbcol=seagreen]
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void
>
>.
>
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
No, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald
|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sq...y_triggers.htm
but may not be completely safe.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you want to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened?". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>
>.
>
|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message[vbcol=seagreen]
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void
>
>.
>
Avoid update without where clause
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =transaction mode. When OFF, it returns the connection to autocommit =transaction mode.
When a connection is in implicit transaction mode and the connection is =not currently in a transaction, executing any of the following =statements starts a transaction:
-- Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> > Using SQL 7.0,
> > Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where > clause ? > > In order to avoid devastating lapse of memory :o\
> > Thanks !
> > Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
> > Hi every one,
> >
> > Using SQL 7.0,
> >
> > Without using transaction, is there a way that SQL void
> > (refuse) any update or delete if there is no where
> > clause ?
> >
> > In order to avoid devastating lapse of memory :o\
> >
> > Thanks !
> >
> > Donald|||I agree with Mark completely
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*)
Then the DELETE statement
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted
Then we use a "-- ROLLBACK COMMIT" as the final line
This is commented out so that you have to DBLCLICK on the keyword that you want to use
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>> Hi every one,
>> Using SQL 7.0,
>> Without using transaction, is there a way that SQL void
>> (refuse) any update or delete if there is no where
>> clause ?
>> In order to avoid devastating lapse of memory :o\
>> Thanks !
>> Donald
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
>> Donald,
>> You could get into the habit of always executing things
on the live
>> servers like this:
>> BEGIN TRAN
>> <your DML code>
>> <some select statements to verify your DML code>
>> When you are satisfied, issue a COMMIT TRAN, or if you
cocked it up,
>> issue a ROLLBACK TRAN. I always do this when making
changes to
>> production, even if they have been scripted and tested.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Donald wrote:
>> > Hi every one,
>> >
>> > Using SQL 7.0,
>> >
>> > Without using transaction, is there a way that SQL
void
>> > (refuse) any update or delete if there is no where
>> > clause ?
>> >
>> > In order to avoid devastating lapse of memory :o\
>> >
>> > Thanks !
>> >
>> > Donald
>
>.
>|||One way I achieved this in the past was to create triggers
on the table(s) in question and make sure the rowcount
wasn't over a certain threshold. Some tables might only
have 1 row updated at a time and never more than that,
others maybe 5 or 10. So I made the trigger check the
rowcount for this.
Van
>--Original Message--
>Hi every one,
>Using SQL 7.0,
>Without using transaction, is there a way that SQL void
>(refuse) any update or delete if there is no where
>clause ?
>In order to avoid devastating lapse of memory :o\
>Thanks !
>Donald
>.
>|||Very good points Steve! I agree with you completely too, and you put it
much better than I did.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> I agree with Mark completely.
> We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
> Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
> Then the DELETE statement.
> Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
> Then we use a "-- ROLLBACK COMMIT" as the final line.
> This is commented out so that you have to DBLCLICK on the keyword that you want to use.
> Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
> We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =transaction mode. When OFF, it returns the connection to autocommit =transaction mode.
When a connection is in implicit transaction mode and the connection is =not currently in a transaction, executing any of the following =statements starts a transaction:
-- Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> > Using SQL 7.0,
> > Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where > clause ? > > In order to avoid devastating lapse of memory :o\
> > Thanks !
> > Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
> > Hi every one,
> >
> > Using SQL 7.0,
> >
> > Without using transaction, is there a way that SQL void
> > (refuse) any update or delete if there is no where
> > clause ?
> >
> > In order to avoid devastating lapse of memory :o\
> >
> > Thanks !
> >
> > Donald|||I agree with Mark completely
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*)
Then the DELETE statement
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted
Then we use a "-- ROLLBACK COMMIT" as the final line
This is commented out so that you have to DBLCLICK on the keyword that you want to use
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>> Hi every one,
>> Using SQL 7.0,
>> Without using transaction, is there a way that SQL void
>> (refuse) any update or delete if there is no where
>> clause ?
>> In order to avoid devastating lapse of memory :o\
>> Thanks !
>> Donald
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
>> Donald,
>> You could get into the habit of always executing things
on the live
>> servers like this:
>> BEGIN TRAN
>> <your DML code>
>> <some select statements to verify your DML code>
>> When you are satisfied, issue a COMMIT TRAN, or if you
cocked it up,
>> issue a ROLLBACK TRAN. I always do this when making
changes to
>> production, even if they have been scripted and tested.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Donald wrote:
>> > Hi every one,
>> >
>> > Using SQL 7.0,
>> >
>> > Without using transaction, is there a way that SQL
void
>> > (refuse) any update or delete if there is no where
>> > clause ?
>> >
>> > In order to avoid devastating lapse of memory :o\
>> >
>> > Thanks !
>> >
>> > Donald
>
>.
>|||One way I achieved this in the past was to create triggers
on the table(s) in question and make sure the rowcount
wasn't over a certain threshold. Some tables might only
have 1 row updated at a time and never more than that,
others maybe 5 or 10. So I made the trigger check the
rowcount for this.
Van
>--Original Message--
>Hi every one,
>Using SQL 7.0,
>Without using transaction, is there a way that SQL void
>(refuse) any update or delete if there is no where
>clause ?
>In order to avoid devastating lapse of memory :o\
>Thanks !
>Donald
>.
>|||Very good points Steve! I agree with you completely too, and you put it
much better than I did.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> I agree with Mark completely.
> We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
> Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
> Then the DELETE statement.
> Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
> Then we use a "-- ROLLBACK COMMIT" as the final line.
> This is commented out so that you have to DBLCLICK on the keyword that you want to use.
> Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
> We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
Avoid update without where clause
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_s...by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
>|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN.
Especially when doing the work in PRODUCTION - but regardless - on the TES
T BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "detai
ls" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from t
he first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you w
ant to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor"
joins can increase row presentation, this is extremely important - you might
have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "wha
t just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD fi
le for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1383601c443ff$12727930$a001280a@.phx
.gbl...
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void[vbcol=seagreen]
>
>.
>
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_s...by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx
.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
>|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN.
Especially when doing the work in PRODUCTION - but regardless - on the TES
T BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "detai
ls" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from t
he first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you w
ant to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor"
joins can increase row presentation, this is extremely important - you might
have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "wha
t just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD fi
le for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1383601c443ff$12727930$a001280a@.phx
.gbl...
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void[vbcol=seagreen]
>
>.
>
Avoid triggers in condition
I have a insert/update trigger on MYTABLE.
Is it possible to avoid/deactive this trigger under certain conditions? For
example, when I run a stored procedure.
Any ideas? Maybe setting somekind of temporary variable and unsetting it
after the store proc has run?
Thanks!On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote:
>I have a insert/update trigger on MYTABLE.
>Is it possible to avoid/deactive this trigger under certain conditions? For
>example, when I run a stored procedure.
>Any ideas? Maybe setting somekind of temporary variable and unsetting it
>after the store proc has run?
>Thanks!
>
Hi Kirsten
ALTER TABLE TableName
DISABLE TRIGGER TriggerName
do something
ALTER TABLE TableName
ENABLE TRIGGER TriggerName
Beware that this will disable the trigger for all connections. If a user
updates the table just when you are running the script, then the trigger
won't fire for him/her either.
A better option is to use the trigger only for code that has to be run
for in ALL circumstances, and put code that is needed only when end
users update the data in the stored procedure they call to make the
modifications.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Kirsten,
A trigger can be enabled/disabled for a table by using the ALTER TABLE
statment.
However, TMK, a trigger will always fire when the action that the trigger is
defined for occurs and the trigger is enabled.
HTH
Jerry
"Kirsten" <norep@.norep.com> wrote in message
news:OEzZ%23j8vFHA.3860@.TK2MSFTNGP09.phx.gbl...
>I have a insert/update trigger on MYTABLE.
> Is it possible to avoid/deactive this trigger under certain conditions?
> For
> example, when I run a stored procedure.
> Any ideas? Maybe setting somekind of temporary variable and unsetting it
> after the store proc has run?
> Thanks!
>|||Ok.
What about if the condition is bases on a column?
For example, how to ask inside the trigger the following?
if only column 4 is updated then do nothing
else do everything.
Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:61e6j19h308utjgsgcm3uios961hlmv896@.
4ax.com...
> On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote:
>
For
> Hi Kirsten
> ALTER TABLE TableName
> DISABLE TRIGGER TriggerName
> do something
> ALTER TABLE TableName
> ENABLE TRIGGER TriggerName
> Beware that this will disable the trigger for all connections. If a user
> updates the table just when you are running the script, then the trigger
> won't fire for him/her either.
> A better option is to use the trigger only for code that has to be run
> for in ALL circumstances, and put code that is needed only when end
> users update the data in the stored procedure they call to make the
> modifications.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Kirsten,
Use the IF UPDATE (column) AND/OR syntax. See the CREATE TRIGGER statement
in SQL BOL.
HTH
Jerry
"Kirsten" <norep@.norep.com> wrote in message
news:O$XKZu8vFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Ok.
> What about if the condition is bases on a column?
> For example, how to ask inside the trigger the following?
> if only column 4 is updated then do nothing
> else do everything.
> Thanks!
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:61e6j19h308utjgsgcm3uios961hlmv896@.
4ax.com...
> For
>|||Look up SET CONTEXT_INFO in BOL.
"Kirsten" <norep@.norep.com> wrote in message
news:OEzZ%23j8vFHA.3860@.TK2MSFTNGP09.phx.gbl...
>I have a insert/update trigger on MYTABLE.
> Is it possible to avoid/deactive this trigger under certain conditions?
> For
> example, when I run a stored procedure.
> Any ideas? Maybe setting somekind of temporary variable and unsetting it
> after the store proc has run?
> Thanks!
>|||Maybe this is what you need:
http://www.aspfaq.com/show.asp?id=2016
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uOa6xM9vFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Look up SET CONTEXT_INFO in BOL.
> "Kirsten" <norep@.norep.com> wrote in message
> news:OEzZ%23j8vFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
Is it possible to avoid/deactive this trigger under certain conditions? For
example, when I run a stored procedure.
Any ideas? Maybe setting somekind of temporary variable and unsetting it
after the store proc has run?
Thanks!On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote:
>I have a insert/update trigger on MYTABLE.
>Is it possible to avoid/deactive this trigger under certain conditions? For
>example, when I run a stored procedure.
>Any ideas? Maybe setting somekind of temporary variable and unsetting it
>after the store proc has run?
>Thanks!
>
Hi Kirsten
ALTER TABLE TableName
DISABLE TRIGGER TriggerName
do something
ALTER TABLE TableName
ENABLE TRIGGER TriggerName
Beware that this will disable the trigger for all connections. If a user
updates the table just when you are running the script, then the trigger
won't fire for him/her either.
A better option is to use the trigger only for code that has to be run
for in ALL circumstances, and put code that is needed only when end
users update the data in the stored procedure they call to make the
modifications.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Kirsten,
A trigger can be enabled/disabled for a table by using the ALTER TABLE
statment.
However, TMK, a trigger will always fire when the action that the trigger is
defined for occurs and the trigger is enabled.
HTH
Jerry
"Kirsten" <norep@.norep.com> wrote in message
news:OEzZ%23j8vFHA.3860@.TK2MSFTNGP09.phx.gbl...
>I have a insert/update trigger on MYTABLE.
> Is it possible to avoid/deactive this trigger under certain conditions?
> For
> example, when I run a stored procedure.
> Any ideas? Maybe setting somekind of temporary variable and unsetting it
> after the store proc has run?
> Thanks!
>|||Ok.
What about if the condition is bases on a column?
For example, how to ask inside the trigger the following?
if only column 4 is updated then do nothing
else do everything.
Thanks!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:61e6j19h308utjgsgcm3uios961hlmv896@.
4ax.com...
> On Thu, 22 Sep 2005 19:57:20 -0300, Kirsten wrote:
>
For
> Hi Kirsten
> ALTER TABLE TableName
> DISABLE TRIGGER TriggerName
> do something
> ALTER TABLE TableName
> ENABLE TRIGGER TriggerName
> Beware that this will disable the trigger for all connections. If a user
> updates the table just when you are running the script, then the trigger
> won't fire for him/her either.
> A better option is to use the trigger only for code that has to be run
> for in ALL circumstances, and put code that is needed only when end
> users update the data in the stored procedure they call to make the
> modifications.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Kirsten,
Use the IF UPDATE (column) AND/OR syntax. See the CREATE TRIGGER statement
in SQL BOL.
HTH
Jerry
"Kirsten" <norep@.norep.com> wrote in message
news:O$XKZu8vFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Ok.
> What about if the condition is bases on a column?
> For example, how to ask inside the trigger the following?
> if only column 4 is updated then do nothing
> else do everything.
> Thanks!
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:61e6j19h308utjgsgcm3uios961hlmv896@.
4ax.com...
> For
>|||Look up SET CONTEXT_INFO in BOL.
"Kirsten" <norep@.norep.com> wrote in message
news:OEzZ%23j8vFHA.3860@.TK2MSFTNGP09.phx.gbl...
>I have a insert/update trigger on MYTABLE.
> Is it possible to avoid/deactive this trigger under certain conditions?
> For
> example, when I run a stored procedure.
> Any ideas? Maybe setting somekind of temporary variable and unsetting it
> after the store proc has run?
> Thanks!
>|||Maybe this is what you need:
http://www.aspfaq.com/show.asp?id=2016
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uOa6xM9vFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Look up SET CONTEXT_INFO in BOL.
> "Kirsten" <norep@.norep.com> wrote in message
> news:OEzZ%23j8vFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
Avoid Looping / Cursors. Help with Statement.
Hi Folks,
I have two tables, one of which I want to update from another.
Essentiall I have a table of orders and a product table. I want to
subtract the qty sold in the orders table from the QtyInStock column in
the Products table, for every line in an order.
But I dont really want to loop through each line in the order, either
in application or with a cursor as something is telling me there must
be a neater solution!
Example Orders Table.
OrderID ProductID Qty
1, 104, 2
1, 199, 1
2, 100, 3
3, 858, 1
ProductID, QtyInStock
104, 3
199, 1
etc ...
As you can see I want to be able to run a query against OrderID 1,
and it to reduce the QtyInStock column by the correct amount for
products 104 and 199 as an example.
Can this be done with one statement, or will I have to loop ?
Thanks in Advance.
Craig.Hi
CREATE TABLE #Test1
(
OrderID int,
ProductID int,
Qty int
)
INSERT INTO #Test1 VALUES (1,104,2)
INSERT INTO #Test1 VALUES (1,199,1)
INSERT INTO #Test1 VALUES (2,100,3)
INSERT INTO #Test1 VALUES (3,828,1)
CREATE TABLE #Test2
(
ProductID int,
QtyInStock int
)
INSERT INTO #Test2 VALUES (104,3)
INSERT INTO #Test2 VALUES (199,1)
UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
WHERE EXISTS (SELECT * FROM #Test2
T WHERE T.ProductID=#Test1.ProductID)
DROP TABLE #Test1,#Test2
<craig.parsons@.crawfos.com> wrote in message
news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column in
> the Products table, for every line in an order.
> But I dont really want to loop through each line in the order, either
> in application or with a cursor as something is telling me there must
> be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
> Thanks in Advance.
>
> Craig.
>|||Uri,
I think Craig wants to update the quantity in stock
from the Product table, not the quantity in the Orders
table, which your query updates. He could use Jens's
solution, or one like this:
UPDATE #Products SET
QtyInStock = QtyInStock - (
SELECT SUM(O.Qty)
FROM #Orders AS O
WHERE O.ProductID = #Products.ProductID
)
WHERE EXISTS (
SELECT * FROM #Orders
WHERE #Orders.ProductID = #Products.ProductID
)
Steve Kass
Drew University
Uri Dimant wrote:
>Hi
>CREATE TABLE #Test1
>(
> OrderID int,
> ProductID int,
> Qty int
> )
>INSERT INTO #Test1 VALUES (1,104,2)
>INSERT INTO #Test1 VALUES (1,199,1)
>INSERT INTO #Test1 VALUES (2,100,3)
>INSERT INTO #Test1 VALUES (3,828,1)
>
>CREATE TABLE #Test2
>(
> ProductID int,
> QtyInStock int
> )
>INSERT INTO #Test2 VALUES (104,3)
>INSERT INTO #Test2 VALUES (199,1)
>
>UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
>FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
>WHERE EXISTS (SELECT * FROM #Test2
>T WHERE T.ProductID=#Test1.ProductID)
>
>
>DROP TABLE #Test1,#Test2
>
>
><craig.parsons@.crawfos.com> wrote in message
>news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
>
>
>|||craig.parsons@.crawfos.com wrote:
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column
> in the Products table, for every line in an order.
> But I dont really want to loop through each line in the order,
> either in application or with a cursor as something is telling me
> there must be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
Here is the ANSI version (I used the Sum function to guarantee only a single
result would be returned):
UPDATE Products
SET QtyInStock = QtyInStock -
(SELECT Sum(Qty) FROM Orders o
WHERE o.OrderID = 1 AND o.ProductID = Products.ProductID)
The T-SQL version:
UPDATE p
SET QtyInStock = QtyInStock - o.Qty
FROM Products p inner join (
SELECT ProductID,Sum(Qty) AS Qty FROM Orders
WHERE OrderID = 1 GROUP BY ProductID) o
ON o.ProductID = p.ProductID
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.
I have two tables, one of which I want to update from another.
Essentiall I have a table of orders and a product table. I want to
subtract the qty sold in the orders table from the QtyInStock column in
the Products table, for every line in an order.
But I dont really want to loop through each line in the order, either
in application or with a cursor as something is telling me there must
be a neater solution!
Example Orders Table.
OrderID ProductID Qty
1, 104, 2
1, 199, 1
2, 100, 3
3, 858, 1
ProductID, QtyInStock
104, 3
199, 1
etc ...
As you can see I want to be able to run a query against OrderID 1,
and it to reduce the QtyInStock column by the correct amount for
products 104 and 199 as an example.
Can this be done with one statement, or will I have to loop ?
Thanks in Advance.
Craig.Hi
CREATE TABLE #Test1
(
OrderID int,
ProductID int,
Qty int
)
INSERT INTO #Test1 VALUES (1,104,2)
INSERT INTO #Test1 VALUES (1,199,1)
INSERT INTO #Test1 VALUES (2,100,3)
INSERT INTO #Test1 VALUES (3,828,1)
CREATE TABLE #Test2
(
ProductID int,
QtyInStock int
)
INSERT INTO #Test2 VALUES (104,3)
INSERT INTO #Test2 VALUES (199,1)
UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
WHERE EXISTS (SELECT * FROM #Test2
T WHERE T.ProductID=#Test1.ProductID)
DROP TABLE #Test1,#Test2
<craig.parsons@.crawfos.com> wrote in message
news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column in
> the Products table, for every line in an order.
> But I dont really want to loop through each line in the order, either
> in application or with a cursor as something is telling me there must
> be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
> Thanks in Advance.
>
> Craig.
>|||Uri,
I think Craig wants to update the quantity in stock
from the Product table, not the quantity in the Orders
table, which your query updates. He could use Jens's
solution, or one like this:
UPDATE #Products SET
QtyInStock = QtyInStock - (
SELECT SUM(O.Qty)
FROM #Orders AS O
WHERE O.ProductID = #Products.ProductID
)
WHERE EXISTS (
SELECT * FROM #Orders
WHERE #Orders.ProductID = #Products.ProductID
)
Steve Kass
Drew University
Uri Dimant wrote:
>Hi
>CREATE TABLE #Test1
>(
> OrderID int,
> ProductID int,
> Qty int
> )
>INSERT INTO #Test1 VALUES (1,104,2)
>INSERT INTO #Test1 VALUES (1,199,1)
>INSERT INTO #Test1 VALUES (2,100,3)
>INSERT INTO #Test1 VALUES (3,828,1)
>
>CREATE TABLE #Test2
>(
> ProductID int,
> QtyInStock int
> )
>INSERT INTO #Test2 VALUES (104,3)
>INSERT INTO #Test2 VALUES (199,1)
>
>UPDATE #Test1 SET Qty=(SELECT QtyInStock-Qty
>FROM #Test2 T WHERE T.ProductID=#Test1.ProductID)
>WHERE EXISTS (SELECT * FROM #Test2
>T WHERE T.ProductID=#Test1.ProductID)
>
>
>DROP TABLE #Test1,#Test2
>
>
><craig.parsons@.crawfos.com> wrote in message
>news:1136476091.334221.282250@.g44g2000cwa.googlegroups.com...
>
>
>|||craig.parsons@.crawfos.com wrote:
> Hi Folks,
> I have two tables, one of which I want to update from another.
> Essentiall I have a table of orders and a product table. I want to
> subtract the qty sold in the orders table from the QtyInStock column
> in the Products table, for every line in an order.
> But I dont really want to loop through each line in the order,
> either in application or with a cursor as something is telling me
> there must be a neater solution!
> Example Orders Table.
> OrderID ProductID Qty
> 1, 104, 2
> 1, 199, 1
> 2, 100, 3
> 3, 858, 1
> ProductID, QtyInStock
> 104, 3
> 199, 1
> etc ...
> As you can see I want to be able to run a query against OrderID 1,
> and it to reduce the QtyInStock column by the correct amount for
> products 104 and 199 as an example.
> Can this be done with one statement, or will I have to loop ?
>
Here is the ANSI version (I used the Sum function to guarantee only a single
result would be returned):
UPDATE Products
SET QtyInStock = QtyInStock -
(SELECT Sum(Qty) FROM Orders o
WHERE o.OrderID = 1 AND o.ProductID = Products.ProductID)
The T-SQL version:
UPDATE p
SET QtyInStock = QtyInStock - o.Qty
FROM Products p inner join (
SELECT ProductID,Sum(Qty) AS Qty FROM Orders
WHERE OrderID = 1 GROUP BY ProductID) o
ON o.ProductID = p.ProductID
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.
Sunday, February 12, 2012
auto-summary columns
In a lot of cases for the sake of performance I use UPDATE, INSERT, and
DELETE triggers for maintaining a denormalized column in another table that
stores summary information (such as an inventory transaction table and a
total on-hand balance column in an item master table).
I was thinking, since I've made lots of variations of this all of which are
basically the same in form, it would be convenient for MS to supply a
special 'summary' column type that automatically monitors the other table's
column being summarized and stays up to date so I don't have to create
triggers every time. I know you can get summaries just by writing an SP or
view to retrieve them, but that's really inefficient when the table being
summarized gets large.
I'm posting this idea on the off chance I've missed a feature in SS2K that
does something like this already... also, does anyone know if this is a
feature known to be coming in Yukon?
TIA,
BobHi
Have you looked at Computed Columns?
BOL has info on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob" <noone@.nowhere.com> wrote in message
news:OHRHI$CIFHA.2136@.TK2MSFTNGP14.phx.gbl...
> In a lot of cases for the sake of performance I use UPDATE, INSERT, and
> DELETE triggers for maintaining a denormalized column in another table
that
> stores summary information (such as an inventory transaction table and a
> total on-hand balance column in an item master table).
> I was thinking, since I've made lots of variations of this all of which
are
> basically the same in form, it would be convenient for MS to supply a
> special 'summary' column type that automatically monitors the other
table's
> column being summarized and stays up to date so I don't have to create
> triggers every time. I know you can get summaries just by writing an SP or
> view to retrieve them, but that's really inefficient when the table being
> summarized gets large.
> I'm posting this idea on the off chance I've missed a feature in SS2K that
> does something like this already... also, does anyone know if this is a
> feature known to be coming in Yukon?
> TIA,
> Bob
>|||Have you considered using indexed views?
Always maintain a healthy degree of skepticism in the face of arguments
that favour denormalization "for the sake of performance".
Denormalization is a trade off. One query's performance is improved but
elsewhere performance and integrity suffers. Denormalization can also
be a slippery slope toward more denormalization. Certainly if you
denormalize "in a lot of cases" then I suggest you take a long hard
look at whether you have the correct design and implementation. There
are usually better solutions.
David Portas
SQL Server MVP
--|||Second that, You should only denormalize AFTER a performance problem has
surfaced in a normalized data structure, and then only after examining all
the other options. There's a;most always a way to improve performance in a
normalized database schema, using properly designed and optimized indices.
And even if that approach isn;t sufficient to deal with the problem,
denormalization applied to a fully normalized schema will always be more
effective and successful than denormalization done out of the gate.
"David Portas" wrote:
> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||>> I use UPDATE, INSERT, and DELETE triggers for maintaining a
denormalized column in another table that stores summary information
(such as an inventory transaction table and a
total on-hand balance column in an item master table). <<
The only reason to store summary infomation is that this is a data
warehouse, that is so big that the recomputation would be too
expensive. But the data is static in a DW.
I would stick with nice, portable and always correct VIEWs instead of
proprietary triggers that fire everytime the table is touched.|||The funny thing is that for the cost of the coding you could probably
upgrade hardware enough to avoid the whole denormalization thing :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109888063.340480.52940@.g14g2000cwa.googlegroups.com...
> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||All of my practical experience indicates to me that denormalization is a
good thing for storing aggregate information; I've seen it to be simple,
nothing but reliable, and dramatically increase read performance without
significantly impacting write performance.
As such, I don't find your argument compelling (partly because I'm not
concerned with portability). I'm clearly not one of such distinction and
experience as yourself; I would ask you to elaborate your point of view so I
can better understand it.
Bob
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109898958.939621.87460@.o13g2000cwo.googlegroups.com...
> denormalized column in another table that stores summary information
> (such as an inventory transaction table and a
> total on-hand balance column in an item master table). <<
> The only reason to store summary infomation is that this is a data
> warehouse, that is so big that the recomputation would be too
> expensive. But the data is static in a DW.
> I would stick with nice, portable and always correct VIEWs instead of
> proprietary triggers that fire everytime the table is touched.
>|||>> All of my practical experience indicates to me that denormalization
is a good thing for storing aggregate information; I've seen it to be
simple, nothing but reliable, and dramatically increase read
performance without significantly impacting write performance. <<
I have seen the opposite. Triggers constantly firing slow things down.
The time to write a value is orders of magnitude greater than the time
to compute it. The extra storage starts to add up. Etc. But more
than that, data integrity gets shot in the foot. Example: Orders carry
the total in a column that is supposed to equal the sum of the order
details. I put a trigger on OrderDetails to modify Orders. But I have
no trigger on Orders, so someone can change that total directly -- and
they will. As I start to use only triggers for data integrity, I find
that more and more business rules need more than one trigger apiece.
That is a little hard in SQL Server and a serious problem in more
powerful SQL products that have BEFORE and AFTER, as well as multiple
trigger options.
not concerned with portability). I'm clearly not one of such
distinction and experience as yourself; I would ask you to elaborate
your point of view so I can better understand it. <<
Portability and standard code are always issues. You port from one
release of the same software to another. You hire programmers who do
not know your local dialect. Unless the company business plan is to
stagnate and die, you will port and maintain code -- this is 80% of the
total cost of a system over its lifetime. Pros write code for other
people and amateurs write code to amuse themselves.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
> <...>
Points taken, thank you.
> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
I have no problem telling my customers, 'Microsoft only'. So far I've
received nothing but nods of approval. I will port and maintain code, but
only to other MS products.
I know, I'm going to hell...
Bob|||I have never found anything I couldn't do using Microsoft's triggers. I
agree completely that keeping summary data is usually wrong, and I have only
had one case where it was necessary. We had a manufacturing application
that calculated stuff that took the last fifty readings and the last fifty
calculated values into consideration (one SQL Statement was 200+lines.)
Needless to say that it took way too long to recalculate these values on
demand. So we had a trigger call the summary procedure when values were
entered.
Either way, it is always my advice to never denormalize your data for
performance until you have exhausted all of the usual tips. Indexing, views
(indexed too,) correct hardware, well built apps, etc.first. If it is
needed, it is needed, but seldom is that true.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110035642.897731.285480@.z14g2000cwz.googlegroups.com...
> is a good thing for storing aggregate information; I've seen it to be
> simple, nothing but reliable, and dramatically increase read
> performance without significantly impacting write performance. <<
> I have seen the opposite. Triggers constantly firing slow things down.
> The time to write a value is orders of magnitude greater than the time
> to compute it. The extra storage starts to add up. Etc. But more
> than that, data integrity gets shot in the foot. Example: Orders carry
> the total in a column that is supposed to equal the sum of the order
> details. I put a trigger on OrderDetails to modify Orders. But I have
> no trigger on Orders, so someone can change that total directly -- and
> they will. As I start to use only triggers for data integrity, I find
> that more and more business rules need more than one trigger apiece.
> That is a little hard in SQL Server and a serious problem in more
> powerful SQL products that have BEFORE and AFTER, as well as multiple
> trigger options.
>
> not concerned with portability). I'm clearly not one of such
> distinction and experience as yourself; I would ask you to elaborate
> your point of view so I can better understand it. <<
> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
>
DELETE triggers for maintaining a denormalized column in another table that
stores summary information (such as an inventory transaction table and a
total on-hand balance column in an item master table).
I was thinking, since I've made lots of variations of this all of which are
basically the same in form, it would be convenient for MS to supply a
special 'summary' column type that automatically monitors the other table's
column being summarized and stays up to date so I don't have to create
triggers every time. I know you can get summaries just by writing an SP or
view to retrieve them, but that's really inefficient when the table being
summarized gets large.
I'm posting this idea on the off chance I've missed a feature in SS2K that
does something like this already... also, does anyone know if this is a
feature known to be coming in Yukon?
TIA,
BobHi
Have you looked at Computed Columns?
BOL has info on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob" <noone@.nowhere.com> wrote in message
news:OHRHI$CIFHA.2136@.TK2MSFTNGP14.phx.gbl...
> In a lot of cases for the sake of performance I use UPDATE, INSERT, and
> DELETE triggers for maintaining a denormalized column in another table
that
> stores summary information (such as an inventory transaction table and a
> total on-hand balance column in an item master table).
> I was thinking, since I've made lots of variations of this all of which
are
> basically the same in form, it would be convenient for MS to supply a
> special 'summary' column type that automatically monitors the other
table's
> column being summarized and stays up to date so I don't have to create
> triggers every time. I know you can get summaries just by writing an SP or
> view to retrieve them, but that's really inefficient when the table being
> summarized gets large.
> I'm posting this idea on the off chance I've missed a feature in SS2K that
> does something like this already... also, does anyone know if this is a
> feature known to be coming in Yukon?
> TIA,
> Bob
>|||Have you considered using indexed views?
Always maintain a healthy degree of skepticism in the face of arguments
that favour denormalization "for the sake of performance".
Denormalization is a trade off. One query's performance is improved but
elsewhere performance and integrity suffers. Denormalization can also
be a slippery slope toward more denormalization. Certainly if you
denormalize "in a lot of cases" then I suggest you take a long hard
look at whether you have the correct design and implementation. There
are usually better solutions.
David Portas
SQL Server MVP
--|||Second that, You should only denormalize AFTER a performance problem has
surfaced in a normalized data structure, and then only after examining all
the other options. There's a;most always a way to improve performance in a
normalized database schema, using properly designed and optimized indices.
And even if that approach isn;t sufficient to deal with the problem,
denormalization applied to a fully normalized schema will always be more
effective and successful than denormalization done out of the gate.
"David Portas" wrote:
> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||>> I use UPDATE, INSERT, and DELETE triggers for maintaining a
denormalized column in another table that stores summary information
(such as an inventory transaction table and a
total on-hand balance column in an item master table). <<
The only reason to store summary infomation is that this is a data
warehouse, that is so big that the recomputation would be too
expensive. But the data is static in a DW.
I would stick with nice, portable and always correct VIEWs instead of
proprietary triggers that fire everytime the table is touched.|||The funny thing is that for the cost of the coding you could probably
upgrade hardware enough to avoid the whole denormalization thing :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109888063.340480.52940@.g14g2000cwa.googlegroups.com...
> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||All of my practical experience indicates to me that denormalization is a
good thing for storing aggregate information; I've seen it to be simple,
nothing but reliable, and dramatically increase read performance without
significantly impacting write performance.
As such, I don't find your argument compelling (partly because I'm not
concerned with portability). I'm clearly not one of such distinction and
experience as yourself; I would ask you to elaborate your point of view so I
can better understand it.
Bob
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109898958.939621.87460@.o13g2000cwo.googlegroups.com...
> denormalized column in another table that stores summary information
> (such as an inventory transaction table and a
> total on-hand balance column in an item master table). <<
> The only reason to store summary infomation is that this is a data
> warehouse, that is so big that the recomputation would be too
> expensive. But the data is static in a DW.
> I would stick with nice, portable and always correct VIEWs instead of
> proprietary triggers that fire everytime the table is touched.
>|||>> All of my practical experience indicates to me that denormalization
is a good thing for storing aggregate information; I've seen it to be
simple, nothing but reliable, and dramatically increase read
performance without significantly impacting write performance. <<
I have seen the opposite. Triggers constantly firing slow things down.
The time to write a value is orders of magnitude greater than the time
to compute it. The extra storage starts to add up. Etc. But more
than that, data integrity gets shot in the foot. Example: Orders carry
the total in a column that is supposed to equal the sum of the order
details. I put a trigger on OrderDetails to modify Orders. But I have
no trigger on Orders, so someone can change that total directly -- and
they will. As I start to use only triggers for data integrity, I find
that more and more business rules need more than one trigger apiece.
That is a little hard in SQL Server and a serious problem in more
powerful SQL products that have BEFORE and AFTER, as well as multiple
trigger options.
not concerned with portability). I'm clearly not one of such
distinction and experience as yourself; I would ask you to elaborate
your point of view so I can better understand it. <<
Portability and standard code are always issues. You port from one
release of the same software to another. You hire programmers who do
not know your local dialect. Unless the company business plan is to
stagnate and die, you will port and maintain code -- this is 80% of the
total cost of a system over its lifetime. Pros write code for other
people and amateurs write code to amuse themselves.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
> <...>
Points taken, thank you.
> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
I have no problem telling my customers, 'Microsoft only'. So far I've
received nothing but nods of approval. I will port and maintain code, but
only to other MS products.
I know, I'm going to hell...
Bob|||I have never found anything I couldn't do using Microsoft's triggers. I
agree completely that keeping summary data is usually wrong, and I have only
had one case where it was necessary. We had a manufacturing application
that calculated stuff that took the last fifty readings and the last fifty
calculated values into consideration (one SQL Statement was 200+lines.)
Needless to say that it took way too long to recalculate these values on
demand. So we had a trigger call the summary procedure when values were
entered.
Either way, it is always my advice to never denormalize your data for
performance until you have exhausted all of the usual tips. Indexing, views
(indexed too,) correct hardware, well built apps, etc.first. If it is
needed, it is needed, but seldom is that true.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110035642.897731.285480@.z14g2000cwz.googlegroups.com...
> is a good thing for storing aggregate information; I've seen it to be
> simple, nothing but reliable, and dramatically increase read
> performance without significantly impacting write performance. <<
> I have seen the opposite. Triggers constantly firing slow things down.
> The time to write a value is orders of magnitude greater than the time
> to compute it. The extra storage starts to add up. Etc. But more
> than that, data integrity gets shot in the foot. Example: Orders carry
> the total in a column that is supposed to equal the sum of the order
> details. I put a trigger on OrderDetails to modify Orders. But I have
> no trigger on Orders, so someone can change that total directly -- and
> they will. As I start to use only triggers for data integrity, I find
> that more and more business rules need more than one trigger apiece.
> That is a little hard in SQL Server and a serious problem in more
> powerful SQL products that have BEFORE and AFTER, as well as multiple
> trigger options.
>
> not concerned with portability). I'm clearly not one of such
> distinction and experience as yourself; I would ask you to elaborate
> your point of view so I can better understand it. <<
> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
>
Subscribe to:
Posts (Atom)