How to insert SQL with data from a Query?

Question

How to insert SQL with data from a Query?

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

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

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

The value of the field tipo is 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:

The data in the CODIGO_USUARIO column must be inserted in the busqueda1, resultado1 fields. The data in the Electrodomestico column must be inserted in the resultado2 field and the data in the Cant column must be inserted in the resultado3

field

How can I do this?

sql sql-server

asked by Jose Dario Correa 03.05.2018 в 22:25

source

2 answers

2

The returned values of 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 do not 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 only 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:

link

answered by 03.05.2018 в 22:36

Remove Information from a String How to fill an array with respect to the values of another javascript array?

score 1 · Accepted 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