Select and then Insert

Question

Select and then Insert

Navigation

#1 by (2 votes)

2

I need to do a procedure stored in MySQL:

I have a select where I rescue data:

 select columna1, columna1, columna1, columna1, columna1 from TB1

and these data taken I need to pass them to a new table ( TB2 ):

Talves is as simple as I think (but I have not tried):

 INSERT INTO TB2 (columna1,columna2,columna3,columna4,columna5) VALUES (TB1.columna1,TB1.columna2,TB1.columna3,TB1.columna4,TB1.columna5)

I do not want to set every column in a variable, because the only data can be duplicated and throw me "subquery returns more than 1 value" error

DEVELOPED:

  CREATE DEFINER = 'root'@'localhost'
  PROCEDURE SELECCIONAR_INSERTAR
  BEGIN

  DECLARE NEW_COL1 VARCHAR(10);
  DECLARE NEW_COL2 VARCHAR(10);
  DECLARE NEW_COL3 VARCHAR(10);
  DECLARE NEW_COL4 VARCHAR(10);
  DECLARE NEW_COL5 VARCHAR(10);



SELECT NEW_COL1=COLUMNA1,
       NEW_COL2=COLUMNA2,
       NEW_COL3=COLUMNA3,
       NEW_COL4=COLUMNA4,
       NEW_COL5=COLUMNA5
FROM   TB1;





INSERT INTO TB2 (COLUMNA1,
                 COLUMNA2,
                 COLUMNA3,
                 COLUMNA4,
                 COLUMNA5) VALUES (NEW_COL1,
                                   NEW_COL2,
                                   NEW_COL3,
                                   NEW_COL4,
                                   NEW_COL5);

  END

This does not fill anything, you have to take into account that the select of the TB1 brings thousands of data ....

mysql stored-procedure

asked by Francisco Acevedo 12.09.2018 в 18:25

source

1 answer

In which cases the IMEI of a mobile device is not available? Return a list of an object

score 2 · Accepted Answer

For this you have a clause of INSERT INTO... SELECT that allows you to insert directly the result of a query. Obviously they have to match quantity, types and lengths of columns between the query and the table where you are going to insert.

In your case, the syntax would be something like this:

INSERT INTO TB2 (COLUMNA1,
                 COLUMNA2,
                 COLUMNA3,
                 COLUMNA4,
                 COLUMNA5)
SELECT COLUMNA1,
       COLUMNA2,
       COLUMNA3,
       COLUMNA4,
       COLUMNA5
FROM   TB1;