0

I have searched all the google and not yet found a solution. I seen some Joint functions etc in some sites but don't know how to do well.

I have table1 having 192 columns and 4000 records.

Table2 with 402 column and 0 records.

I need to add columns which is not present on table1 from table2. I found very hard to manually add column one by one.

Example:

Table1 (4000 records) Columns: Name, Age, Gender

Table2 (0 records) Columns: ID, Refferal_ID, Name, Age, Place, Gender, Country

I need to make Table3 Table3 (4000 records) Columns: ID, Refferal_ID, Name, Age, Place, Gender, Country (Let new column's value for each record to be 0)

MBK
  • 2,589
  • 21
  • 25
  • 1
    You can use prepare a statement and execute un `alter table ...`. Check a look here, https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html – fingerprints Apr 03 '17 at 20:15
  • @amaia Sorry, I am new to SQL did not understand how to implement. Can you describe briefly. [I saw this question](http://stackoverflow.com/questions/27376152/how-to-add-a-column-to-a-table-from-another-table-in-mysql) but Actually I need to add columns not the data. Hard to add columns manually. – MBK Apr 04 '17 at 02:17

1 Answers1

0

If you want to add a column, you can create a procedure with a prepared statement using prepareStmt.

This is an example using prepared statements for add a column in a table:

SET v_column_definition := CONCAT(
  v_column_name
  ,' ',v_column_type
  ,' ',v_column_options 
);

SET @stmt := CONCAT('ALTER TABLE ADD COLUMN ', v_column_definition);

PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Graham
  • 7,431
  • 18
  • 59
  • 84
fingerprints
  • 2,751
  • 1
  • 25
  • 45
  • How to get Column Name, type (60+) of respective columns to execute which is present on the other table. Also Is there any ready made tool or php scripts etc which can do this job. Give 2 tables will create a new table with both of table combined (all column and data) – MBK Apr 04 '17 at 16:46
  • you can do something like this and get the structure of the columns in any table `SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' [AND table_schema = 'db_name'] [AND column_name LIKE 'wild']` – fingerprints Apr 04 '17 at 16:50