INSERT based on conditional logic

• polkadot

  SSChampion

  Points: 13664

  More actions

  April 4, 2018 at 8:27 pm

  #334198

  Need a conditional INSERT / UPDATE  where more rows are inserted into new table than are present in original   How many is based on the value given in Qty column.  Is this possible?

  Original table:
  

  USE [TestDB]

  GO

  CREATE TABLE [dbo].[BulkTbl](

  [Make] [varchar](50) NULL,

  [Model] [varchar](50) NULL,

  [Qty] [int] NULL,

  [SubTotal] [int] NULL

  )

  INSERT INTO [dbo].[BulkTbl] ([Make], [Model], [Qty], [SubTotal])

  VALUES

  (N'Samsung', N'qr989', 1, 90),

  (N'Samsung', N're234', 2, 200),

  (N'Nokia', N'blrb', 2, 160);

  New table multiples rows based on value given in Qty,  so if Qty = 1 then insert same row, but if Qty = 2 then insert two rows, give SubTotal  as SubTotal divided by Qty, and since grain is on Make/Model, in new table Qty is always 1:
  

  select 'Make','Model','Qty', 'SubTotal'

  UNION ALL

  select 'Samsung','qr989','1', '90'

  UNION ALL

  select 'Samsung','re234','1', '100'

  UNION ALL

  select 'Samsung','re234','1', '100'

  UNION ALL

  select 'Samsung','blrb','1', '80'

  UNION ALL

  select 'Samsung','blrb','1', '80'

  
  Possible? How?
  

  --Quote me

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  April 5, 2018 at 2:05 am

  #1985593

  Here is a quick suggestion that should get you passed this hurdle.
  😎

  
USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID('dbo.BulkTbl') IS NOT NULL DROP TABLE dbo.BulkTbl;
CREATE TABLE [dbo].[BulkTbl](
 [Make] [varchar](50) NULL,
 [Model] [varchar](50) NULL,
 [Qty] [int] NULL,
 [SubTotal] [int] NULL
)

  INSERT INTO [dbo].[BulkTbl] ([Make], [Model], [Qty], [SubTotal])
VALUES
(N'Samsung', N'qr989', 1, 90),
(N'Samsung', N're234', 2, 200),
(N'Nokia', N'blrb', 2, 160);

  ;WITH NUMS(N) AS (SELECT X.N FROM (VALUES (1),(2),(3),(4),(5))X(N))
SELECT
  BD.Make
 ,BD.Model
 ,1 AS [Qty]
 ,BD.SubTotal / BD.Qty AS [SubTotal]
FROM  dbo.BulkTbl  BD
CROSS APPLY NUMS NM
WHERE NM.N <= BD.Qty;


  Output
  
Make  Model Qty SubTotal
--------- ------ ---- ---------
Samsung qr989 1  90
Samsung re234 1  100
Samsung re234 1  100
Nokia  blrb 1  80
Nokia  blrb 1  80