0

I'm currently doing a student attendance program using c# and MySQL.

I created a database called std_info where it keeps students information and there are nibm_id, nic, name,address,number,batch in that table

2nd table name is std_att to get student attendance and the columns are nibm_id, nic, name, batch

What i want is to retrive only nibm_id, nic, name, batch from std_info table to std_att The primary key is nibm_id

here is what i was trying to do so far

cmd.CommandText = "insert into std_att (nibm_id, nic, name, batch) SELECT * (nibm_id, nic, name, batch)  FROM `std_info` where nibm_id like '" + textBox1.Text + "%'";

Please show me a way to do this. Thank You!

Sachintha Senanayake
  • 29
  • 5
  • Does this answer your question? [Insert into ... values ( SELECT ... FROM ... )](https://stackoverflow.com/questions/25969/insert-into-values-select-from) – Marceli Wac Apr 14 '21 at 07:53
  • If it was me, I'd start with the sql – Strawberry Apr 14 '21 at 07:56
  • @MarceliWac answers only for SQL Server – Sachintha Senanayake Apr 14 '21 at 08:00
  • 1
    May I ask. Why are you have duplicate names in the tables. So far I only see that std_info is having some extra info compare to std_att. There for I don't see the use of std_att. I would also advice you to just full out write the words for attribute names an table names. this makes it way harder to read and understand. – Diceble Apr 14 '21 at 08:12
  • @SachinthaSenanayake, are you saying you are using SQL Server, or that the question I suggested only solves your problem when it's running on SQL Server? Asking, because [this answer](https://stackoverflow.com/a/25971/5683522) cleary states that it works for MySQL (which I assumed you're using). – Marceli Wac Apr 14 '21 at 08:16
  • You haven't explained what the problem is here... – Ian Kemp Apr 14 '21 at 08:23

1 Answers1

2
cmd.CommandText = "insert into std_att (nibm_id, nic, name, batch) SELECT nibm_id, nic, name, batch  FROM `std_info` where nibm_id = '" + textBox1.Text + "%'";

There are obviously a lot better ways to get this done, and the above is vulnerable to SQL injections etc, but I can understand if this is for a simple quick piece of work.

Like some of the users have said, don't have duplicate columns in std_att table since they are already in the main table std_info

I would also try to find the nibm_id before the insert bit, to validate in-app and not let the db freak out on it's side (if nibm_id can't be found)

Stuart
  • 32
  • 7