CODEWITHSUNDEEP
mysql
user3379655
user3379655

Reputation: 258

How to copy table's data to another duplicate table with incremented primary key and some of the column with incremented value. in mySQL

I have a table "Channels" which contain one primary ID column(i.e 1,2,3,..), one Incremented SPL_ID column (i.e 1000,1001,1002,...) and 30 other columnn contain different records.

What I need : insert all Channels data in dummy, then copy dummy's record back to Channels, so we have (i.e let say )10000 records, then we can make with this records 20000, 30000 and so on...

I created another dummy table "dummy", insert all Channels data to dummy.

 Create table dummy like Channels;
 Insert into dummy select * from Channels;

Now i need to insert all dummy data back to Channels table.

  insert into Channels select * from dummy.

PROBLEM : primary key and, how to manage splColumn so it can have record value after last field's value , i.e last field 1110 then it should 1111,1112,1113...

just for expl :

Table : Channels

id -- spl -- Othercolumn

1 -- 10 -- abcd

2 -- 11 -- abcde

now insert dummy records back to Channels at that time need SPL value 12, 13 ..etc

Upvotes: 0

Views: 1142

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

While copying bulk records from one table to another, you can't generate sequence numbers on any not auto_incremented column. Specifically when table and data are generated all together using wildcard select. And for any table creation with selected fields, you can't specify a column to assign values in a sequence from a specific value.

To achieve this, you need either a stored procedure or create the table first with desired fields and then insert into it.

Example:

Step 1: 

create table prime_copy( col1 ..., col2 ...,   
                         spl int not null auto_increment primary key,  
                         colX ..., colY ... );

Step 2: 

select max( splColumn ) + 1 into @ai from prime_table;  
set @sql := concat( 'alter table prime_copy auto_increment=', @ai );  
prepare stmt from @sql;
execute stmt;
drop prepare stmt;

Step 3: 

-- reset the table, but not auto increment value
delete from prime_copy; 

insert into prime_copy( col1, col2, NO_Spl_Col_here, but_other_required )
  select colM, colN, NO_Spl_Col, but_other_required from prime_table;

insert into prime_table( col, names, splCol, as_required )
  select col, names, auto_incremented_spl_col, as_required );

Repeat the Step3 until the desired number of records are inserted into the prime_table.

Upvotes: 1

Related Questions