help with a query

Posted on 2007-08-10
Last Modified: 2013-11-05
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

Question by:repzat
    LVL 39

    Accepted 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


    Author Comment

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

    Thanks and regards

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now