While Loop insert from select statement

Question

I need to create parent child table in my DB. My child table is already existed for a long time, so it contained a long list of records. What i want to do it copy child name to my parents table.

Child Table

---------------
ChildID | ChildNm
---------------
1        |A
2        |B
3        |C

Parent Table

----------------
ParentID|ParentNm|ChildNm
----------------

Query

WHILE (
        SELECT Min(ChildID)
        FROM ChildTable
        ) <
    SELECT Max(ChildID)
    FROM ChildTable

BEGIN
    --INSERT every child NAME TO my parents TABLE
END

Is this the best possible way to do this?

What if there are multiple children per parent? THe parent table allows only one ChildNm per parent. Also, you can make it look the same with a view and a JOIN — gbn
– gbn, Commented Jul 2, 2013 at 7:51
yes, the table allow multiple child, but current requirement is to insert a parent for every child, cause the parent table is new table~ — Low Chee Mun
– Low Chee Mun, Commented Jul 2, 2013 at 8:07

t-clausen.dk · Accepted Answer · 2013-07-02 08:06:45Z

1

I don't see a need for loops, I rarely do.

Try something like this perhaps:

insert parent(ChildNm)
select distinct ChildNm from child c
where not exists (select 1 from parent where c.childNm = childNm)

select * from parent

I am not sure what you want as parent names

*I am assuming your parent table looks something like this:

create table parent(ParentID int identity(1,1), ParentNm char(1), ChildNm char(1))

answered Jul 2, 2013 at 8:06

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandeep Kumar · Accepted Answer · 2013-07-02 08:20:57Z

1

Architecture wise this table relationship is not good and completely against the law.

I don't know what purpose you want to solve using this implementation.

According to good approach and practice you need to Add a column "ParentId" in Child table.

And against the every child you can set the parent ID. I am not sure how you will decide that this clild blongs to which parent.

I would suggest to rethink about your approach first.

By the way you can use bulk insert query to insert value in parent table like:

   insert into parents (childnm) select  ChildNm from Child group by ChildNm

edited Jul 2, 2013 at 8:20

answered Jul 2, 2013 at 8:13

Sandeep Kumar

8031 gold badge6 silver badges13 bronze badges

Collectives™ on Stack Overflow

While Loop insert from select statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related