locked
Deleted Values Insert into a New Table RRS feed

  • Question

  • Hi All,

    I have Database DB and Table CB ..CB has 3  dependent Tables OB ,CP,CI
    So My Requirement is ..

    delete  From CB
    where ID_NUM not Like '99%'

    But here delete will  not work because of FK .. so i need to delete records from OB CP CI Tables first ...and I need to store all the Deleted Records of all the 4 Tables (CB,OB,CP,CI) in a seperate Table
    May be all 4 deleted values in a Table
    or
    each table deleted records in each Seperate newly created table..



    all i Know to go each child table and delete but ....It may be time Running Script ...
    Or
    Individuall for Each delete and Store in new table



    Can any one Help me on This .....

    Wednesday, September 22, 2010 12:43 AM

Answers

  • The easier way for you will be to set up relationship to cascade for all the dependent tables. This way when you delete a customer from the customers table all related records in dependent tables are deleted automatically.

    If you need to do this manually, take a look at this blog post

    Delete all data in database (when you have FKs) (second method) as how to delete records in tables in the proper order.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Muhammad Abbas Thursday, September 23, 2010 6:24 AM
    • Marked as answer by Ai-hua Qiu Thursday, September 30, 2010 8:51 AM
    Wednesday, September 22, 2010 6:49 PM

All replies

  • SQL Server 2005 and up:

     

    declare @DelT table (FK int)
    
    begin transaction
     try
     delete from OB OUTPUT Deleted.FK into @DelT
     where exists (select 1 from CB where ID_NUM not like
     '99%' where CB.PK = OB.FK)
    
     delete CP from CP inner join @DelT D
     on CP.FK = D.FK
     
    -- same for CI
    
     -- Now final delete
     
      delete from CB where ID_NUM not like '99%'
    
     end try
    
     begin catch
    
      rollback transaction
     ...
     end catch
    
    end transaction
    
    
    
    This is from the top of my head. Take also a look at this blog 

     

    How to insert information into multiple related tables and return ID using SQLDataSource

    take a look at transaction and error handling in this sample procedure. The logic will be the same for DELETE case.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 22, 2010 12:53 AM

  • Thank You very Much for the Reply ,But Declare @Del table (FK INT)...

    I need to store in a Physical table ....

     

    Will @Del create a table and where does it store

    I dont know that ...and i need to have it in real Physical table

    not in # temp Table or ## Global table ...

    Can you please let me know


    Wednesday, September 22, 2010 1:16 AM
  • The table does not have to be a temp table. You can use a real table

    if object_ID('DelT','U') IS not null

       drop table DelT

    create table DelT (FK int)

    and then use DelT table instead of @DelT in the code I showed.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 22, 2010 1:27 AM
  • Thank You Mam and I DO Appreciate that ...

     

    Wednesday, September 22, 2010 1:42 AM
  • hi this is my updated requirement actually

     

    HI, I need some help from you guys ...Please

     

    My requirement is

    I have a database called DB

    USE DB

     

    SELECT COUNT (*) FROM CUSTOMER WHERE SSN NOT LIKE'88%"

    ----Result:8990

    SELECT CUSTOMER_ID FROM CUSTOMER
    WHERE SSN NOT LIKE '88%'

    ---(i will have 8990 Customer)IDs

     

    DELETE FROM TABLE CUSTOMER

    WHERE SSN NOT LIKE '88%'

    and here i have to store the deleted results it in a new physical table ...but here is the issue

    I have columns called Customer_ID and ORDER_ID ( THERE ARE 28 TABLES DEPENDENT ON CUSTOMER TABLE

    Dependent Tables:  Fullfiullement( Customer_ID,Order_ID,Phone))

    PERSONAL( CUSTOMER_ID,PHONE)

    ADDRESS( CUSTOMER_ID,ADDRESS)

    etc etc

    dependent or referencing other tables ..so i have to DROP the Child table first with taking the Customer _ID and order_IDs from above ...

     

     

    SO i need to write a Script which will delete the Child tables and then Parent tables and i have to Save each deleted tables Data ........

    I will  have :Customer_ID   

    SELECT CUSTOMER_ID FROM CUSTOMER
    WHERE SSN NOT LIKE '88%'

    ---(i will have 8990 Customer_ID )

     

     

    and i need to delete only the records of those customer_IDS

     

    Please help me ...If you have any questions let know

     

     

     

    Wednesday, September 22, 2010 3:17 PM
  • The easier way for you will be to set up relationship to cascade for all the dependent tables. This way when you delete a customer from the customers table all related records in dependent tables are deleted automatically.

    If you need to do this manually, take a look at this blog post

    Delete all data in database (when you have FKs) (second method) as how to delete records in tables in the proper order.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Muhammad Abbas Thursday, September 23, 2010 6:24 AM
    • Marked as answer by Ai-hua Qiu Thursday, September 30, 2010 8:51 AM
    Wednesday, September 22, 2010 6:49 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html 

    Tables have row and not records. Tables van REFERENCE each other but there is no such thing as a "dependent table" in SQL or RDBMS. 

    >> I need to store all the Deleted Records [sic: rows are not records] of all the 4 Tables (CB,OB,CP,CI) in a seperate Table << 

    since rows from four different tables would represent distinct and different kinds of entities, this would be like putting automobiles and squids in the same pot. RDBMS has no "child table" concept; we have REFERENCE instead. What you posted makes no sense from an RDBMS viewpoint. You do not even known the right words to use.

    You can use an OUTPUT clause to capture the deleted rows from each DELETE FROM statement as stinking kludge. But your schema and your mindset are screwed up so badly that you cannot trust your data. You need more help than you can get in a forum. Please get an education before you program again. 

    --CELKO-- Please post DDL, use ISO-11179 naming rules and format code so we can read it
    Wednesday, September 22, 2010 8:18 PM
    • Proposed as answer by Muhammad Abbas Thursday, September 23, 2010 6:24 AM
    Thursday, September 23, 2010 6:23 AM