-1

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.

Sean Lange
  • 33,028
  • 3
  • 25
  • 40
PhatLee
  • 37
  • 9
  • https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 – Sean Lange May 15 '18 at 18:16
  • 1
    I have read this now about 6 or 7 times and I have no idea what you trying to do. You ask "Can anyone suggest me how to do it please?". But I can't for the life of me figure out what "it" is. You need a LOT more information here. This is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Sean Lange May 15 '18 at 18:21
  • @Sean, so "All_Fruits" is an empty table. All I want to do is migrate all data from "All Fruits" to "All_Fruits". After I do that, when I query "All_Fruits" table, data retrieved should look like above "All_Fruits" table. I am reading the article you suggested, I will try to update my question again and post all the codes I have. Thank you – PhatLee May 15 '18 at 18:46

1 Answers1

0

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
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • You are right, my tables are only examples (which turn out to be pretty bad ones), but your solution is exactly what I was looking for. I did the table join and insert but looking at your solution I can see where I did mistake. Thank you so much! – PhatLee May 15 '18 at 21:05
  • If this works you should consider marking this as the answer. Glad to hear you found a solution. – Sean Lange May 16 '18 at 13:13