MySQL : add data from another database, starting on specific row, user_id is primary, need to insert incrementing numbers starting from 20

0
votes

I have an user table with 19 rows (first row is admin). I need to add more, so I have another database with a table having more than 1.400.000 users.

My table has an "user_id" as primary key, INT(11), no auto-increment. I need to add users starting in row 20, and only "first_name", "last_name" and "email".

My first try:

INSERT INTO mydatabase.users (first_name, last_name, email) SELECT first_name, last_name, email FROM anotherdatabase.users

That gets me a "Field 'user_id' doesn't have a default value".

I understand it is because user_id is primary key and cannot be null. So, again, it is int(11), non auto-increment.

So I want to add 20,21,22,23 and so on, along the other data. I searched a lot for about 5 hours and can´t seem to find anything I can understand.

Thank you in advance.

3
mysqlinsertrowincrement
Why not use auto_increment? - Strawberry
Because I´m managing it from my php code. Our teacher asked us not to use auto_increment. - Martincho
If php is pertinent to the question you should include a php tag. - P.Salmon
You are right. I just wanted to make my question as clean as possible, but good point. - Martincho

3 Answers

1
votes

To get your numbers starting from 20:

INSERT INTO mydatabase.users (user_id, first_name, last_name, email) 
  SELECT 19+row_number() over (),
         first_name, 
         last_name, 
         email 
  FROM anotherdatabase.users

19+row_number() over ()
'row_number()' = Number of current row within its partition (Mysql documentation) 'over ()' - defines a partition without condition; so in fact counts for the entire table

Details for both can be found under MySQL Window Function concepts and syntax
19+ - ...

Alternatively - if the MySQL version < 8.0:

INSERT INTO mydatabase.users (user_id, first_name, last_name, email) 
  SELECT (select 20+count(*) 
          from  anotherdatabase.users u1 where u1.id < u.id) as id,
         first_name, 
         last_name, 
         email 
  FROM anotherdatabase.users u

This does rather assume that you have a user_id on the other table as well. The inner select just counts how many records are in the original database that have a smaller id than the record that is being returned at that point (and adds 20 to have the numbering start at 20).

And a third option (not depending on the existence of user_id):

INSERT INTO mydatabase.users (user_id, first_name, last_name, email) 
  SELECT ( @row_num:=@row_num+1 AS user_id,
         first_name, 
         last_name, 
         email 
  FROM anotherdatabase.users u,
       (SELECT @row_num:=19) var;

I'm not a great fan of this as it involves creation of variables to keep track of the row number.

0
votes

Write a procedure that use variable. for i < 1400020 , you would need to count the number of rows in anotherdatabase.users then add the figure with 20.

DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
  DECLARE i INT DEFAULT 20;

  WHILE i < 1400020 DO
   INSERT INTO mydatabase.users (user_id, first_name, last_name, email) 
   SELECT i,first_name, last_name,  email  FROM anotherdatabase.users;
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL insert_test_data();
DROP PROCEDURE insert_test_data;
0
votes

I found a simpler way, using variables. I still can´t believe it. It worked like a charm

SET @i=19;
INSERT INTO mydatabase.users(user_id, first_name, last_name, email) 
         SELECT @i:=@i+1,
         first_name, 
         last_name, 
         email 
FROM anotherdatabase.users;