"sqlvs" <sqlvs@myway.com> wrote in message
news:ae94e5e1.0307201818.565b7fc4@posting.google.com...
> 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.

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