question

MansoorMohammed-9831 avatar image
0 Votes"
MansoorMohammed-9831 asked MansoorMohammed-9831 commented

Complicated SQL Insert

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


162587-image.png



162611-image.png







CREATE TABLE [dbo].[Table1](

[EmpNo] [int] NULL,

[EmpName] [nchar](10) NULL,

[DeptName] [nchar](10) NULL,

[Location] [nchar](10) NULL,

[AmountPaid] [int] NULL,

[FederalTax] [int] NULL,

[CountyTax] [int] NULL,

[PropertyRatio] [int] NULL,

[Deductable] [int] NULL,

[TaxCode] [nchar](10) 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](10) NULL,

[DeptName] [nchar](10) NULL,

[Location] [nchar](10) NULL,

[AmountPaid] [int] NULL,

[FederalTax] [int] NULL,

[CountyTax] [int] NULL,

[PropertyRatio] [int] NULL,

[Deductable] [int] NULL,

[TaxCode] [nchar](10) NULL

) ON [PRIMARY]

GO


sql-server-general
image.png (18.3 KiB)
image.png (23.2 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered MansoorMohammed-9831 commented
 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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thnk you Tom

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered MansoorMohammed-9831 edited

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Is it not possible to have the EmpNo in an order as I have shown in the pic(Expected output)?
Please ignore the identity seed for EmpNo in Table2, if thats stopping.

0 Votes 0 ·