Home / Questions / 161351
Accepted
Jose Dario Correa
Asked: 2020-05-04 12:25:05 +0800 CST 2020-05-04 12:25:05 +0800 CST

How to insert SQL with data from a Query?

  • 772

I need to insert data in one table into another table. Basically I need to do this:

INSERT INTO Consulta (tipo, busqueda1, resultado1, resultado2, resultado3)

The value of the field tipois defined by me, but the values ​​of the other fields are extracted from the following query:

SELECT CODIGO_USUARIO, Electrodomestico, Cant FROM (SELECT CODIGO_USUARIO, [Abanico Mesa], [Abanico Techo], [Estufa Elect 1F] FROM TempCensoElect) p UNPIVOT (Cant FOR Electrodomestico IN ([Abanico Mesa], [Abanico Techo], [Estufa Elect 1F])) AS unpvt

Resulting in the following:

enter image description here

The column data CODIGO_USUARIOmust be inserted into the fields busqueda1, resultado1. The column data Electrodomesticomust be inserted into the field resultado2and the column data Cantmust be inserted into the fieldresultado3

How can I do this?

sql

2 Answers

  1. The values ​​returned from your select must have the same order and number of columns inserted in your insert .

    I see 1 problem in your logic:

    You are inserting type, but you don't have any value to insert type in your select, if you are not going to extract it then remove it from your insert or add some value to your query

    Outside of that you just need to add an alias for the user_code field to be able to be inserted in two fields

    select codigo_usuario as cu1, Codigo_usuario as cu2, electrodomestico,cant...

    Reference :

    https://www.w3schools.com/sql/sql_insert_into_select.asp

    • 2
  2. Best Answer

    The query would look something like this:

    INSERT INTO Consulta

SELECT 'Condicion para tipo' as Tipo,CODIGO_USUARIO as busqueda1,CODIGO_USUARIO as resultado1,
       Electrodomestico as resultado2, Cant as resultado3
FROM 
        (SELECT CODIGO_USUARIO, [Abanico Mesa], [Abanico Techo], [Estufa Elect 1F] 
        FROM TempCensoElect) p UNPIVOT (Cant FOR Electrodomestico IN ([Abanico Mesa], 
        [Abanico Techo], [Estufa Elect 1F])) AS unpvt
    • 2