how to insert multiple rows in a table using different subqueries

Question

I have a couple of tables

CREATE TABLE [dbo].[prods](
    [IdProducto] [int] IDENTITY(1,1) NOT NULL,
    [IdGrupo] [int] NULL,
    [IdCategoria] [int] NULL,
    [IdAlmacen] [varchar](50) NULL,
    [Codigo] [varchar](50) NULL,
    [Nombre] [varchar](50) NULL,
    [Descripcion] [varchar](max) NULL,
    [Cantidad] [int] NULL,
    [Imagen] [varchar](max) NULL,
    [StockMin] [int] NULL,
    [StockMax] [int] NULL,
    [Ancho] [varchar](50) NULL,
    [Alto] [varchar](50) NULL,
    [Largo] [varchar](50) NULL,
    [Peso] [varchar](50) NULL,
    [Volumen] [varchar](50) NULL,
    [Color] [varchar](50) NULL,
    [Material] [varchar](50) NULL,
    [Presentacion] [varchar](50) NULL,
    [bitPrecioVentaUnico] [int] NULL,
    [PrecioCompra] [money] NULL,
    [DescuentoCompra] [float] NULL,
    [PrecioVenta] [money] NULL,
    [DescuentoVenta] [float] NULL,
    [Estado] [varchar](20) NULL
)

and

CREATE TABLE [dbo].[prodnuevos](
    [Codigo] [int] NULL,
    [itemid] [int] NULL,
    [Item] [varchar](255) NULL,
    [Categoria] [varchar](255) NULL,
    [Cantidad] [int] NULL,
    [Minima] [nvarchar](255) NULL,
    [Costo] [money] NULL,
    [Valor] [money] NULL,
    [peso] [float] NULL,
    [unidades] [float] NULL
)

both tables are somewhat similar, both have a list of products, prodnuevos (which we could consider Spanish for New Products), has a more extended list than prods(Let's call it Products)

what I want to do is use a query that will insert all rows that are available in New Products that are not available in Products...

I have tried this long query

INSERT INTO prods (IdGrupo, IdAlmacen, Codigo, Nombre, Descripcion, Cantidad, Imagen, StockMin,StockMax, Ancho, Alto, Largo, Peso, Volumen, Color, Material, Presentacion, bitPrecioVentaUnico, PrecioCompra, DescuentoCompra, PrecioVenta, DescuentoVenta, Estado) 
VALUES (1, 'Ninguno', (Select prodnuevos.Codigo FROM prodnuevos LEFT JOIN prods ON prodnuevos.Item = prods.Nombre WHERE prods.Nombre IS NULL), (Select prodnuevos.Item FROM prodnuevos LEFT JOIN prods ON prodnuevos.Item = prods.Nombre WHERE prods.Nombre IS NULL), '', (Select prodnuevos.Cantidad FROM prodnuevos LEFT JOIN prods ON prodnuevos.Item = prods.Nombre WHERE prods.Nombre IS NULL), '', 0, 0, '', '', '', '', '', '', '', 'Ninguno', 0, (Select prodnuevos.Costo FROM prodnuevos LEFT JOIN prods ON prodnuevos.Item = prods.Nombre WHERE prods.Nombre IS NULL), 0, 0, 0, 'Activo');

when I try this, I get the following error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I wonder how can I use this query properly.

By the way, Is there also a way that I can use less subqueries instead of repeating it various times?

thank you

Kermit · Accepted Answer · 2013-01-07 23:47:47Z

2

You got the right idea, just approaching the problem the wrong way. You should first SELECT a result set that will get you the data that you wish to copy over, then prefix it with your INSERT like so:

INSERT INTO table VALUES (col1, col2)
SELECT col1, col2 FROM table2

You also don't need to include columns in your INSERT ... VALUES list that have a default and don't exist in your child table.

answered Jan 7, 2013 at 23:47

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to insert multiple rows in a table using different subqueries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related