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