Complicated SQL Insert

Question

I want to insert into select from Table1 to Table2

If the DeptName is xx, the record should be inserted twice with no entry for the

location, FederalTax, CountyTax and PropertyRatio

Table1 has no constraints

Table2 has EmpNo with Identity seed

Below are the calculations for

Amount paid for EmpNo3 is Amount paid-CountTax (333-3=330)

Amount Paid for EmpNo4 is the CountyTax 3

FederalTax for EmpNo3 is the AmountPaid * PropertyRatio/100 (330*3/100=108.9)

if the DeptName is xx then deductable for duplicated recored is 999

if the DeptName is xx then deductable for duplicated recored is XXX

CREATE TABLE [dbo].[Table1](

[EmpNo] [int] NULL,

[EmpName] nchar NULL,

[DeptName] nchar NULL,

[Location] nchar NULL,

[AmountPaid] [int] NULL,

[FederalTax] [int] NULL,

[CountyTax] [int] NULL,

[PropertyRatio] [int] NULL,

[Deductable] [int] NULL,

[TaxCode] nchar NULL

) ON [PRIMARY]

GO

INSERT INTO [Table1] VALUES (1,'Name1','aa','usa',111,91,1,11,101,'ABC')

INSERT INTO [Table1] VALUES (2,'Name2','bb','uk',222,92,2,22,102,'ABC')

INSERT INTO [Table1] VALUES (3,'Name3','xx','Ind',333,93,3,33,103,'ABC')

INSERT INTO [Table1] VALUES (4,'Name4','cc','Ksa',444,94,4,44,104,'ABC')

INSERT INTO [Table1] VALUES (5,'Name5','dd','Ger',555,95,5,55,105,'ABC')

INSERT INTO [Table1] VALUES (6,'Name6','ee','usa',666,96,6,66,106,'ABC')

INSERT INTO [Table1] VALUES (7,'Name7','xx','Ksa',777,97,7,77,107,'ABC')

INSERT INTO [Table1] VALUES (8,'Name8','ff','Ger',888,98,8,88,108,'ABC')

INSERT INTO [Table1] VALUES (9,'Name9','gg','Uk',999,99,9,99,109,'ABC')

INSERT INTO [Table1] VALUES (10,'Name10','xx','usa',1110,100,10,110,110,'ABC')

GO

CREATE TABLE [dbo].[Table2](

[EmpNo] [int] IDENTITY(1,1) NOT NULL,

[EmpName] nchar NULL,

[DeptName] nchar NULL,

[Location] nchar NULL,

[AmountPaid] [int] NULL,

[FederalTax] [int] NULL,

[CountyTax] [int] NULL,

[PropertyRatio] [int] NULL,

[Deductable] [int] NULL,

[TaxCode] nchar NULL

) ON [PRIMARY]

GO

Answer 1

INSERT INTO [Table2] (
      [EmpName]
      ,[DeptName]
      ,[Location]
      ,[AmountPaid]
      ,[FederalTax]
      ,[CountyTax]
      ,[PropertyRatio]
      ,[Deductable]
      ,[TaxCode])
SELECT
      [EmpName]
      ,[DeptName]
      ,[Location]
      ,[AmountPaid]
      ,[FederalTax]
      ,[CountyTax]
      ,[PropertyRatio]
      ,[Deductable]
      ,[TaxCode]
FROM (
    SELECT [EmpNo]
          ,[EmpName]
          ,[DeptName]
          ,[Location]
          ,CASE WHEN DeptName = 'xx' THEN [AmountPaid]-[CountyTax] ELSE [AmountPaid] END AS [AmountPaid]
          ,CASE WHEN DeptName = 'xx' THEN [AmountPaid]*[PropertyRatio]/100 ELSE [FederalTax] END AS [FederalTax]
          ,[CountyTax]
          ,[PropertyRatio]
          ,[Deductable]
          ,[TaxCode]
    FROM [Table1]
    UNION ALL
    SELECT [EmpNo]
          ,[EmpName]
          ,[DeptName]
          ,NULL AS [Location]
          ,[CountyTax] AS [AmountPaid]
          ,NULL AS [FederalTax]
          ,NULL AS [CountyTax]
          ,NULL AS [PropertyRatio]
          ,'999' AS [Deductable]
          ,'PAY' AS [TaxCode]
    FROM [Table1]
    WHERE DeptName = 'xx'
) a
ORDER BY EmpNo, [EmpName],[DeptName],[Deductable]

SELECT *
FROM TABLE2

Answer 2

The simplest answer is it just use UNION ALL.

SET IDENTITY_INSERT [Table2] ON;

INSERT INTO [Table2] (
        [EmpNo]
      ,[EmpName]
      ,[DeptName]
      ,[Location]
      ,[AmountPaid]
      ,[FederalTax]
      ,[CountyTax]
      ,[PropertyRatio]
      ,[Deductable]
      ,[TaxCode])
SELECT [EmpNo]
      ,[EmpName]
      ,[DeptName]
      ,[Location]
      ,CASE WHEN DeptName = 'xx' THEN [AmountPaid]-[CountyTax] ELSE [AmountPaid] END AS [AmountPaid]
      ,CASE WHEN DeptName = 'xx' THEN [AmountPaid]*[PropertyRatio]/100 ELSE [FederalTax] END AS [FederalTax]
      ,[CountyTax]
      ,[PropertyRatio]
      ,[Deductable]
      ,[TaxCode]
FROM [Table1]
UNION ALL
SELECT [EmpNo]
      ,[EmpName]
      ,[DeptName]
      ,NULL AS [Location]
      ,[CountyTax] AS [AmountPaid]
      ,NULL AS [FederalTax]
      ,NULL AS [CountyTax]
      ,NULL AS [PropertyRatio]
      ,'999' AS [Deductable]
      ,'PAY' AS [TaxCode]
FROM [Table1]
WHERE DeptName = 'xx'

SET IDENTITY_INSERT [Table2] OFF;

SELECT *
FROM TABLE2