Query

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

query

hi
i have a doubt in sqlserver.
how to delete the duplicate rows with a query in sqlserver
thank youOne choice is to use Cursor.
But remember that Cursors are very expensive to SQL Server.

Else,

You may try to use the UNION (not UNION ALL), with the same table. Put all the returned data into a temporary Table, then you delete your data from original table and insert the values from the temporary table into it.|||First of all, what is structure of your table?
If your table does not have a primary key - too bad... Use Diogo's advice and then check a normalization rules.
If it does - you can try next method:

drop table test
go
create table test (id int primary key,
code varchar(10))
go
insert test values(1,'code1')
insert test values(2,'code2')
insert test values(3,'code1')
insert test values(4,'code3')
insert test values(5,'code4')
insert test values(6,'code5')
insert test values(7,'code6')
insert test values(8,'code3')

select *
--delete
from test
where id in
(select min(Id) from test group by code having count(*)>1)

May be it needs to run last query couple times....

Query

hey,
i have two tables that I want to match, the records that match I want to delete them from the original file and copy them in an other Tabl
Example
Table A Table
1A 3
3B 4
5J 1
All the records that match in that example (1A and 3B), I want to copy them in a new table and delete them only form TableB
I made a join between the two tables, and I have good results. But how to copy and delete them I don't know
Or maybe in Table B I can have a flag that I put on when it is a mached record? But that I nether don't know how to do that in my join query
Thks for help
JacTo copy them to a new table use SELECT.. INTO:
SELECT col1
INTO NewTable
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
To delete them:
DELETE TableB
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Jac" <anonymous@.discussions.microsoft.com> wrote in message
news:4B32C95B-5EB4-4F21-8B58-EF0121E17D02@.microsoft.com...
> hey,
> i have two tables that I want to match, the records that match I want to
delete them from the original file and copy them in an other Table
> Example:
> Table A Table B
> 1A 3B
> 3B 4V
> 5J 1A
>
> All the records that match in that example (1A and 3B), I want to copy
them in a new table and delete them only form TableB.
> I made a join between the two tables, and I have good results. But how to
copy and delete them I don't know?
> Or maybe in Table B I can have a flag that I put on when it is a mached
record? But that I nether don't know how to do that in my join query.
> Thks for help.
> Jacsql

Query

hey,
i have two tables that I want to match, the records that match I want to del
ete them from the original file and copy them in an other Table
Example:
Table A Table B
1A 3B
3B 4V
5J 1A
All the records that match in that example (1A and 3B), I want to copy them
in a new table and delete them only form TableB.
I made a join between the two tables, and I have good results. But how to co
py and delete them I don't know?
Or maybe in Table B I can have a flag that I put on when it is a mached reco
rd? But that I nether don't know how to do that in my join query.
Thks for help.
JacTo copy them to a new table use SELECT.. INTO:
SELECT col1
INTO NewTable
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
To delete them:
DELETE TableB
FROM TableA JOIN TableB ON TableA.col1 = TableB.col1
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Jac" <anonymous@.discussions.microsoft.com> wrote in message
news:4B32C95B-5EB4-4F21-8B58-EF0121E17D02@.microsoft.com...
> hey,
> i have two tables that I want to match, the records that match I want to
delete them from the original file and copy them in an other Table
> Example:
> Table A Table B
> 1A 3B
> 3B 4V
> 5J 1A
>
> All the records that match in that example (1A and 3B), I want to copy
them in a new table and delete them only form TableB.
> I made a join between the two tables, and I have good results. But how to
copy and delete them I don't know?
> Or maybe in Table B I can have a flag that I put on when it is a mached
record? But that I nether don't know how to do that in my join query.
> Thks for help.
> Jac

q; delete files

I need to delete all the *.txt file in a specific folder modified date is
older than 2 months,. How can I do this in a stored procedure?JIM.H. wrote:
> I need to delete all the *.txt file in a specific folder modified date is
> older than 2 months,. How can I do this in a stored procedure?
http://realsqlguy.com/serendipity/a...th-The-Old.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you are capable of SQL Server 2005 you would do that the best with a
CLR procedure. Looping though the files collections and delewting the
appropiate files.
HTH, Jens Suessmyer.
http://www.sqlserver2005.de
--

q; delete files

I need to delete all the *.txt file in a specific folder modified date is
older than 2 months,. How can I do this in a stored procedure?JIM.H. wrote:
> I need to delete all the *.txt file in a specific folder modified date is
> older than 2 months,. How can I do this in a stored procedure?
http://realsqlguy.com/serendipity/archives/9-Out-With-The-Old.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||If you are capable of SQL Server 2005 you would do that the best with a
CLR procedure. Looping though the files collections and delewting the
appropiate files.
HTH, Jens Suessmyer.
--
http://www.sqlserver2005.de
--

Q: trigger and notification

Hello,
How would you create a trigger on a table modification/delete/add and send
an intication do an e-mail address?
Thanks,
JimHi Jim,
I guess you are looking at something like this...
CREATE TRIGGER tr_test
ON yourtable
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'email address',
'Data inserted, modified or deleted from yourtable'
Also look at BOL for "CREATE TRIGGER"
Thanks
Yogish|||I get this error message after calling stored procedure.
xp_sendmail: Either there is no default mail client or the current mail
client cannot fulfill the messaging request. Please run Microsoft Outlook an
d
set it as the default mail client.
"Yogish" wrote:

> Hi Jim,
> I guess you are looking at something like this...
> CREATE TRIGGER tr_test
> ON yourtable
> FOR INSERT, UPDATE, DELETE
> AS
> EXEC master..xp_sendmail 'email address',
> 'Data inserted, modified or deleted from yourtable'
> Also look at BOL for "CREATE TRIGGER"
> --
> Thanks
> Yogish
>|||You need to configure SQL Mail. You can find the steps
outlined in the following article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
-Sue
On Wed, 12 Jan 2005 13:41:06 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I get this error message after calling stored procedure.
>xp_sendmail: Either there is no default mail client or the current mail
>client cannot fulfill the messaging request. Please run Microsoft Outlook a
nd
>set it as the default mail client.
>"Yogish" wrote:
>

Q: trigger and notification

Hello,
How would you create a trigger on a table modification/delete/add and send
an intication do an e-mail address?
Thanks,
JimHi Jim,
I guess you are looking at something like this...
CREATE TRIGGER tr_test
ON yourtable
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'email address',
'Data inserted, modified or deleted from yourtable'
Also look at BOL for "CREATE TRIGGER"
--
Thanks
Yogish|||I get this error message after calling stored procedure.
xp_sendmail: Either there is no default mail client or the current mail
client cannot fulfill the messaging request. Please run Microsoft Outlook and
set it as the default mail client.
"Yogish" wrote:
> Hi Jim,
> I guess you are looking at something like this...
> CREATE TRIGGER tr_test
> ON yourtable
> FOR INSERT, UPDATE, DELETE
> AS
> EXEC master..xp_sendmail 'email address',
> 'Data inserted, modified or deleted from yourtable'
> Also look at BOL for "CREATE TRIGGER"
> --
> Thanks
> Yogish
>|||You need to configure SQL Mail. You can find the steps
outlined in the following article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
-Sue
On Wed, 12 Jan 2005 13:41:06 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I get this error message after calling stored procedure.
>xp_sendmail: Either there is no default mail client or the current mail
>client cannot fulfill the messaging request. Please run Microsoft Outlook and
>set it as the default mail client.
>"Yogish" wrote:
>> Hi Jim,
>> I guess you are looking at something like this...
>> CREATE TRIGGER tr_test
>> ON yourtable
>> FOR INSERT, UPDATE, DELETE
>> AS
>> EXEC master..xp_sendmail 'email address',
>> 'Data inserted, modified or deleted from yourtable'
>> Also look at BOL for "CREATE TRIGGER"
>> --
>> Thanks
>> Yogish

Q: trigger and notification

Hello,
How would you create a trigger on a table modification/delete/add and send
an intication do an e-mail address?
Thanks,
Jim
Hi Jim,
I guess you are looking at something like this...
CREATE TRIGGER tr_test
ON yourtable
FOR INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail 'email address',
'Data inserted, modified or deleted from yourtable'
Also look at BOL for "CREATE TRIGGER"
Thanks
Yogish
|||I get this error message after calling stored procedure.
xp_sendmail: Either there is no default mail client or the current mail
client cannot fulfill the messaging request. Please run Microsoft Outlook and
set it as the default mail client.
"Yogish" wrote:

> Hi Jim,
> I guess you are looking at something like this...
> CREATE TRIGGER tr_test
> ON yourtable
> FOR INSERT, UPDATE, DELETE
> AS
> EXEC master..xp_sendmail 'email address',
> 'Data inserted, modified or deleted from yourtable'
> Also look at BOL for "CREATE TRIGGER"
> --
> Thanks
> Yogish
>
|||You need to configure SQL Mail. You can find the steps
outlined in the following article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
-Sue
On Wed, 12 Jan 2005 13:41:06 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I get this error message after calling stored procedure.
>xp_sendmail: Either there is no default mail client or the current mail
>client cannot fulfill the messaging request. Please run Microsoft Outlook and
>set it as the default mail client.
>"Yogish" wrote: