Geek Answers Handbook

How to insert data from EXECUTE statement in mySql?

I have data in the wp_users , and I want to duplicate data from this table (except for the ID column) into another table called wp_users2 .

If I didn’t like the id column that I want auto increment, I could just do this:

 insert into wp_users2 (select *, NULL as ID from wp_users)

So, I know that I can do this by typing all the column headers except the ID, and manually selecting it as NULL,

 SELECT NULL as id, col2, col3...

but I would like to do it dynamically. I read this great S.O. post on how to do this, and it works, however I cannot figure out how to take the data that it gives me and put in the insert statement.

 INSERT INTO wp_users2 ( SET @sql = CONCAT('SELECT NULL as ID,', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'ID,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'wp_users' AND TABLE_SCHEMA = 'wp1'), ' FROM wp_users'); PREPARE stmt1 FROM @sql; EXECUTE stmt1; )

What is the correct syntax for this?

0
source share
2 answers

As I understand it, id is the AUTO_INCREMENT field.

So try using this script as an example for your task -

 CREATE TABLE table1( id INT(11) NOT NULL AUTO_INCREMENT, column1 VARCHAR(255) DEFAULT NULL, column2 VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE table2( id INT(11) NOT NULL AUTO_INCREMENT, column1 VARCHAR(255) DEFAULT NULL, column2 VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id) ); INSERT INTO table1 VALUES (1, 'c1', 'c2'), (2, 'c3', 'c4'); SET @source_table = 'table1'; SET @target_table = 'table2'; SET @id = 'id'; SET @columns = NULL; SELECT group_concat(column_name) INTO @columns FROM information_schema.columns WHERE table_schema = 'database_name' -- Set your database name here AND table_name = @source_table AND column_name != @id; SET @insert = concat('INSERT INTO ', @target_table, '(', @id, ',', @columns, ') SELECT NULL, ', @columns, ' FROM ', @source_table); PREPARE stmt1 FROM @insert; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;
+2
source 
 set @sql = (select concat('insert into wp_users2 SELECT NULL,', group_concat(column_name),' from ',table_name) from information_schema.columns where table_name = 'wp_users' and table_schema = 'wp1' and column_name != 'id' order by ordinal_position); prepare stmt1 from @sql; execute stmt1; deallocate prepare stmt1;
+2
source

More articles:


All Articles