[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • 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:

SELECT * FROM A,B
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

0
repzat
Asked:
repzat
1 Solution
 
appariCommented:
create procedure backup_Proc
as
begin

       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


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

Thanks and regards
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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