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?

Answer 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))

Answer 2

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