CODEWITHSUNDEEP
mysql
new learner
new learner

Reputation: 15

MYSQL, copy data from one table to another table with increment in varchar

I have Two tables

Table_1 : users

id name
-------
1  john
2  john2
3  john3

Table_2 : tbl_account_level

ID    Code      Detail
------------------------
1     00001     GPS
2     00002     CAMERA
3     00003     Tracking System

Now, i want to copy all the data from users table into tbl_account_level through mysql , and the code number is Varchar .

My Query:

  insert into tbl_account_level (code, detail)select cast("00003" as unsigned) + 1, name from users ;

Result Something Like , 

ID    Code      Detail
------------------------
1     00001     GPS
2     00002     CAMERA
3     00003     Tracking System
4     4         john
5     5         john2
6     6         john3

I want Result Something like

ID    Code      Detail
------------------------
1     00001     GPS
2     00002     CAMERA
3     00003     Tracking System
4     00004     john
5     00005     john2
6     00006     john3

Upvotes: 0

Views: 71

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

You can use lpad for adding the leading 0 after the increment

insert into tbl_account_level (code, detail)
select lpad(cast(code  as unsigned) + 1, 5 ,'0'), name 
from users

and if you need row increment you coudl use a multi statement as 

SET @row_number = 0; 
insert into tbl_account_level (code, detail)
select lpad(cast(code  as unsigned) +  @row_number:=@row_number + 1, 4 ,'0'), name 
from users ;

Upvotes: 1

Related Questions