Can someone help me for a SQL query to delete all the transactions from 2
SQL table?
Here is what I want to do.
"Go thru all the transactions in both table A and B
If table A has a record, delete this record first, then delete the same
record info from B
If table A has no record reference, continue and delete the record in B"
and so on.
Thanks
MC (webmaster@.ozoptics.com) writes:
> Can someone help me for a SQL query to delete all the transactions from 2
> SQL table?
> Here is what I want to do.
> "Go thru all the transactions in both table A and B
> If table A has a record, delete this record first, then delete the same
> record info from B
> If table A has no record reference, continue and delete the record in B"
DELETE B
DELETE A
And most of all, don't delete row by row, delete all in one statement.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||My mistake,
Actually, I wanted to delete the records in both Table A and B, before Oct
4, 2004
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns979040CD7705Yazorman@.127.0.0.1...[vbcol=seagreen]
> MC (webmaster@.ozoptics.com) writes:
2
> DELETE B
> DELETE A
> And most of all, don't delete row by row, delete all in one statement.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||DELETE TableA.*, TableA.DateField
FROM TableA WHERE TableA.DateField < 10/4/2004
"MC" <webmaster@.ozoptics.com> wrote in message
news:OLN%23fw0TGHA.1868@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> My mistake,
> Actually, I wanted to delete the records in both Table A and B, before Oct
> 4, 2004
> MC
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns979040CD7705Yazorman@.127.0.0.1...
from[vbcol=seagreen]
> 2
same[vbcol=seagreen]
B"[vbcol=seagreen]
http://www.microsoft.com/technet/pro...ads/books.mspx
>
sql
Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts
Monday, March 26, 2012
Friday, March 9, 2012
QA cancel
If a query is successfully cancelled in QA, does any part of it commit ?
1. if autocommit is in effect ?
2. if implicit transactions are in effect and the text in QA is as follows :
sql statement
go
commit
go
Hi,
If a query is successfully cancelled in QA, does any part of it commit ?
1. if autocommit is in effect ?
Depends up on the way you are performing the DML (Insert / update / delete)
If you are doing a row by row operation with out Begin Tran...Rollback tran
... commit tran
then SQL server does auto commit on the rows and will rollback only the DML
which was fired during the
time time you click cancel button. Rest will be successfully committed.
(If it is single Insert / Update / Delete chich handle lots of records , in
that case if you press cancel button then all the activities will be
rolled back)
2. if implicit transactions are in effect and the text in QA is as follows :
In this case if you click the cancel , data will be inside the table till
inserted / updated / deleted.
If you issue a Rollback data will be rolled back or if you issue a commit
the data will be committed.
(If it is single Insert / Update / Delete chich handle lots of records , in
that case if you press cancel button then all the activities will be
rolled back if your commit is not fired)
Thanks
Hari
MCDBA
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eiein2pGEHA.3540@.TK2MSFTNGP12.phx.gbl...
> If a query is successfully cancelled in QA, does any part of it commit ?
> 1. if autocommit is in effect ?
> 2. if implicit transactions are in effect and the text in QA is as follows
:
> sql statement
> go
> commit
> go
>
1. if autocommit is in effect ?
2. if implicit transactions are in effect and the text in QA is as follows :
sql statement
go
commit
go
Hi,
If a query is successfully cancelled in QA, does any part of it commit ?
1. if autocommit is in effect ?
Depends up on the way you are performing the DML (Insert / update / delete)
If you are doing a row by row operation with out Begin Tran...Rollback tran
... commit tran
then SQL server does auto commit on the rows and will rollback only the DML
which was fired during the
time time you click cancel button. Rest will be successfully committed.
(If it is single Insert / Update / Delete chich handle lots of records , in
that case if you press cancel button then all the activities will be
rolled back)
2. if implicit transactions are in effect and the text in QA is as follows :
In this case if you click the cancel , data will be inside the table till
inserted / updated / deleted.
If you issue a Rollback data will be rolled back or if you issue a commit
the data will be committed.
(If it is single Insert / Update / Delete chich handle lots of records , in
that case if you press cancel button then all the activities will be
rolled back if your commit is not fired)
Thanks
Hari
MCDBA
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eiein2pGEHA.3540@.TK2MSFTNGP12.phx.gbl...
> If a query is successfully cancelled in QA, does any part of it commit ?
> 1. if autocommit is in effect ?
> 2. if implicit transactions are in effect and the text in QA is as follows
:
> sql statement
> go
> commit
> go
>
Saturday, February 25, 2012
q; how to improve this transaction
Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and one
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessing
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and one
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessing
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh
q; how to improve this transaction
Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and on
e
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessin
g
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and on
e
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessin
g
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh
Subscribe to:
Posts (Atom)