3

I have two table ex: student_old & student_new. I want to copy data from student_old to student_new. The table structures are in two different ways. ex:

student_old

1. stu_id 
2. stu_first_name 
3. stu_middle_name 
4. stu_last_name 
5. stu_course
6. stu_class

student_new

1. student_id
2. student_last_name
3. student_first_name
4. student_address
5. student_dob
6. student_course
7. student_batch
8. student_class

Above is an example only. The table structures cannot be changed because it belong to an institute. There are 1000+ data in the old table. And there is no order between the two tables, as in data of column six of student_old should go to column eight in student_new. I need a SQL query to get data from the old table to the new.

I'm new to SQL. And i tried searching online, but i didn't get ant satisfactory answer. Hope you guys would help.

ekostadinov
  • 6,880
  • 3
  • 29
  • 47
Lahiru Tjay
  • 159
  • 2
  • 4
  • 14
  • what did you search for? which attempts you made? please post some code. – Paolo Sep 16 '14 at 10:27
  • all i could find was when the columns of the two tables are in order. `INSERT INTO newTable (col1, col2, col3) SELECT column1, column2, column3 FROM oldTable` – Lahiru Tjay Sep 16 '14 at 10:32
  • @LahiruTjay that's exactly what you need to do. have you tried it? did it give you errors? – Tanner Sep 16 '14 at 10:33
  • 1
    @LahiruTjay the order of the columns in the INSERT...INTO is under your control, you can move them as you please to suit your needs. the major constraints are that you do must have the same number of fields and matching fields do must have compatible data types – Paolo Sep 16 '14 at 10:34
  • Just modify the order as you require, as long as the column mapping/types is correct: `INSERT INTO student_new (student_id, student_last_name, student_first_name...) SELECT stu_id, stu_last_name, stu_first_name... FROM student_old` – Tanner Sep 16 '14 at 10:35
  • It does not do the way i want. What i gave was a example table. In my case there are null values as well as values to be entered later in the new table. Also only some fields of the old table should be retrieved to the new table. And the number of fields doesn't match also. Hope you understand what im tryn to say – Lahiru Tjay Sep 16 '14 at 10:37
  • 1
    In that case, improve your question to show some sample data in the old table and how you want it to look in the new table. Data types would be useful as well as any assumptions around what to do with null values and other edge cases – Tanner Sep 16 '14 at 10:45

0 Answers0