Data copy from 1 table to another with the foreign key

Question

I have an issue in insertion of records from one table to another the tables schema is same Table 1 Id Parent Id Text 11 Null A 12 Null B 13 11 C 14 11 D 15 13 E The record should be inserted in this format, According to my knowledge I have to copy the foreign key relation not the identities of it. Table 2 Id Parent Id Text 31 Null A 32 Null B 33 31 C 34 31 D 35 33 E

Umang Tandon · Answer

I have found a solution to this question. Steps: Insert the Text column values into the main table, leaving the ParentId as null and at the same time insert the values into a Dictionary as oldId key and New Inserted Id as value to the corresponding key. After insertion Update the values on the basis of the mapped Dictionary keyValue Pair

Umang Tandon · Answer

See the reason behined 31 has two records is that if you see the first table from where the data was copied 11 was there twice in the ParentId and in the Second table 11 has been reflected with the new id as 31 so it has 2 records in the parentId Column

Ronika Jency · Answer

Umang, Understood. Get the latest insert value of id(primary key). And, insert same in parentId for next row. Why are you inserting null, 31 has two records? below one is you dn't need ah? Table 2 Id Parent Id Text 31 Null A 32 31B 33 32 C 34 33 D 35 34 E

Umang Tandon · Answer

Ronika, I am not updating the same table I guess you are misunderstanding my question. The expected output which you have understood is not the right one. The expected ParentId output should be based on the new Id that will be generated Table 2 Id Parent Id Text 31 Null A 32 Null B 33 31 C 34 31 D 35 33 E

Ronika Jency · Answer

Hi Umang, Why are you update the same table primary key value to foriegn key column (same table)? Can you share your query for execute the value? Now, i got your point, Your need is to copy from below table, Table 1 Id Parent Id Text 11 Null A 12 Null B 13 11 C 14 11 D 15 13 E Your expected output is below, Is it right? Table 2 Id Parent Id Text 31 Null A 32 Null B 33 11 C 34 11 D 35 13 E

Umang Tandon · Answer

Hi Ronika, Both the tables have same schema same primary(ID) foreign key(ParentId) realtion. The approach which I am currently using is recursive approach first insert the text and keep the parent id null and after this I will get the newly generated id in the new table and then I will update it (this part I am not able to understand that how i have to do) am I following the right approach? Any help will be appreciated thanks.

Ronika Jency · Answer

Hi, Can you tell me the query used for copy one table to another? And, Are you using the same table primary key refer as a foriegn key?.