Professional Web Applications Themes

Parent-child insert/copy - Microsoft SQL / MS SQL Server

Hello, [I posted this to microsoft.public.sqlserver, but I think it's been phased out? I couldn't locate the group on DevelopersDex.] My basic question is, how can I insert/copy related parent/child rows from one database or set of tables to another? [I've searched old posts for the last couple of hours, but kept finding questions about XML inserts? And BOL examples are too simple.] Here are two sample tables: Parent ------ ParentID [Identity column] ParentName Child ----- ChildID [Identity column] ParentID ChildName There are about twenty parent records I want to copy, with several hundred related child records, so I thought ...

  1. #1

    Default Parent-child insert/copy

    Hello,

    [I posted this to microsoft.public.sqlserver, but I think it's been
    phased out? I couldn't locate the group on DevelopersDex.]

    My basic question is, how can I insert/copy related parent/child rows
    from one database or set of tables to another? [I've searched old
    posts for the last couple of hours, but kept finding questions about
    XML inserts? And BOL examples are too simple.]

    Here are two sample tables:

    Parent
    ------
    ParentID [Identity column]
    ParentName

    Child
    -----
    ChildID [Identity column]
    ParentID
    ChildName

    There are about twenty parent records I want to copy, with several
    hundred related child records, so I thought I'd create a stored
    procedure and just pass in the ParentID for each set. I've tried the
    following but this fails on the "SELECT ChildName" subquery because,
    naturally, it returns more than one value:

    INSERT INTO ParentTemp (ParentTempName)
    SELECT ParentName FROM Parent WHERE ParentID = 3
    INSERT INTO ChildTemp (ParentTempID, ChildTempName)
    SELECT (SELECT SCOPE_IDENTITY()),
    (SELECT ChildName FROM Child WHERE ParentID = 3)

    This seems like it should be a pretty simple and common task, but I
    haven't found the answer yet. As a programmer, I rarely deal with
    large sets of data in this fashion (retrieving the data, yes, but I
    don't think I've ever done multi-level inserts beyond a single parent
    and its related children--most applications just don't work that
    way!), but I'd really like to learn how to do this and similar tasks.
    The destination here is a production database with live data (I have
    back-ups and of course will take the necessary precautions).

    I've thought about creating by hand each Parent record in the
    production database, then hard-coding the created identity values for
    each set of child records, but I imagine there's a one-shot way to get
    this done.

    Also, for future reference, does anyone have any book recommendations
    that might help in this area? I've checked out a number of SQL books
    in the past, but they seem not to have the answers I need (too simple
    or much too specific/pedantic).

    Thank you.
    sqlvs Guest

  2. #2

    Default Re: Parent-child insert/copy

    You can select a variable (or function) together with a column from a table:

    INSERT INTO ParentTemp (ParentTempName)
    SELECT ParentName FROM Parent WHERE ParentID = 3

    INSERT INTO ChildTemp (ParentTempID, ChildTempName)
    SELECT SCOPE_IDENTITY(),
    ChildName FROM Child WHERE ParentID = 3

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "sqlvs" <com> wrote in message
    news:google.com... 


    Jacco Guest

  3. #3

    Default Re: Parent-child insert/copy

    > You can select a variable (or function) together with a column from a table:

    Thanks very much! That did it. :)
    sqlvs Guest

  4. #4

    Default Re: Parent-child insert/copy

    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are. Making a guess at what you meant, you don't have any tables
    becuase you have no keys.

    Let's re-do your tables to have real keys and DRI actions instead of
    that non-relational, proprietary IDENTITY property (it is not a
    column!).

    CREATE TABLE Parents
    (parent_id INTEGER NOT NULL PRIMARY KEY,
    parent_name CHAR(15) NOT NULL);

    CREATE TABLE Children
    (child_id INTEGER NOT NULL,
    parent_id INTEGER NOT NULL
    REFERENCES Parents(parent_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    child_name CHAR(15) NOT NULL,
    PRIMARY KEY (child_id, parent_id));

    If human beings have to use the child_id and parent_id, then take the
    time to design them -- check digits, syntax verification rules, external
    verification rules,etc.

    Put the parents into a file, and then write a program to insert them and
    verify the key, one row at a time (sorry, but this is the only safe way
    to clean up this data). Then do the same with the children and watch
    for the DRI to throw out the orphans that aculate when your schema is
    this poorily designed.

    If this is actually a general hierarchy and not just a two-level one,
    then look at the nested sets model for the structure.

    --CELKO--


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Parent/Child relations - Trying to access child control for save
    By tnt_lu@hotmail.com in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: April 15th, 12:50 PM
  2. Use Parent Column in Child?
    By Allen Davis in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: May 1st, 08:39 PM
  3. Transferring from parent to child
    By TuneRaider2k in forum FileMaker
    Replies: 3
    Last Post: October 9th, 09:40 PM
  4. OO parent/child relationship
    By Gerard in forum PHP Development
    Replies: 20
    Last Post: October 7th, 05:52 PM
  5. Child & Parent forms
    By Roland Wolters in forum ASP.NET General
    Replies: 3
    Last Post: July 23rd, 08:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139