• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

help with a query

Hi, I have this need:

I have 2 tables, between these tables there is a 'one to many' relationship based on the 'guid' (of type uniqueidentifier) field.

> If I want to join the results from the 2 tables I do:

where A.guid = B.guid
and A.deleted = 0

> and if I want to count the records where the field 'deleted' (of type bit) is = 1 I do:

SELECT count(*) FROM A,B
where A.guid = B.guid
and A.deleted = 1

Now I need a query to use *inside a stored procedure and with transaction support* that allows me to

- GET all the records in table B where the field 'deleted' (wich is in table A) is = 1
- COPY the records from table B where 'deleted' is = 1 to another table C with the *same structure*
   of the table B
- DELETE the records from table B where 'deleted' is = 1

Thanks and regards

1 Solution
create procedure backup_Proc

       insert into tableC (field1, field2, field3, field4)
       select B.field1, B.field2, B.field3, B.field4 from  A,B
              where A.guid = B.guid
                     and A.deleted = 1

         delete B from  A,B
              where A.guid = B.guid
                     and A.deleted = 1

repzatAuthor Commented:
perfect appari, the points are yours. Just could you please put also support for transations?

Thanks and regards
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now