Search code examples
sql-serverasp.net-mvc-4ssms-2014

Migrating data from one table to another in SQL Server

enter image description here

Above is the sample table I am working on. I imported tables "All Fruits", "Fruit A", "Fruit B", "Fruit C", and "Fruit D" from access database to SQL database (for brevity I didn't include all table data). I have created new empty table "All_Fruits, and in there I want to migrate data from "All Fruits". Data type for column Fruit A, B, C, and D in "All Fruits" are "(nvarchar(225), null)" and in "All_Fruits" "(nvarchar(225)", not null) As you can see in diagram, I just want IDs for Fruit_A, B, C and D get populated. And in my user application when user insert more data, they can type new name for "Fruit_Veg" and for rest of the data there will be option to select from dropdown list.

I have been able to get my drop down list working, I can add, update and delete data. But I couldn't figure out how to insert "All Fruits" table data into table "All_Fruits". I want to view, edit or delete existing data from my user application.

Can anyone suggest me how to do it please?

As for my user application, I used ASP.NET MVC code first with existing database.

Solution

  • I am hoping your tables are just examples and not actually this design. I am pretty sure that something like this would do what you want.

    select af.ID
    , af.Fruit_Veg
    , a.ID
    , b.ID
    , c.ID
    , d.ID
from [All Fruits] af
join FruitA a on a.A = af.FruitA
join FruitB b on b.B = af.FruitB
join FruitC c on c.C = af.FruitC
join FruitD d on d.D = af.FruitD