如何在计算兴趣后将数据从一个表插入另一个表 [英] how to insert data from one table to another after calculating interest

问题描述

- 我有一个表 myStoreTable 。其中包含4列。

- 我有另一张表 tblFinalTable ，包含5列。

- 我想从 tblmyStoreTable 将数据插入tblFinalTable。

- 但是，不是直接插入，在计算 EmpInterest 之后，它插入到 tblFinalTable 表中。

- 但我在尝试插入所有时遇到问题基于LOANMTH条件计算兴趣后， tblFinalTable 中的行。

- 我是更新。请帮助。谢谢提前。

-- I have a table "myStoreTable".which contain 4 columns.
-- I a have another table "tblFinalTable", which contain 5 columns.
-- I want to insert data into "tblFinalTable" from "tblmyStoreTable".
-- But, not directly inserted,after calculating the "EmpInterest", it inserted into tblFinalTable table.
-- But i am facing problem while try to insert all the rows in tblFinalTable after calculating interest based on "LOANMTH" condition.
-- I am a fresher.please help.Thanks in advance.

CREATE TABLE dbo.tblmyStoreTable
(
	CPFNO int NULL,
	LOANMTH int NULL,
	EMP_OP int NULL,
	L_GRANT int NULL
) 

INSERT INTO tblmyStoreTable(100,0,20000,0)
INSERT INTO tblmyStoreTable(200,2,40000,15000)
INSERT INTO tblmyStoreTable(300,0,50000,0)
INSERT INTO tblmyStoreTable(400,5,70000,20000)
INSERT INTO tblmyStoreTable(500,0,100000,0)

CREATE TABLE dbo.tblFinalTable
(
	CPFNO int NULL,
	LOANMTH int NULL,
	EMP_OP int NULL,
	L_GRANT int NULL,
	EmpInterest INT
) 

declare @InterestRate INT
SET @InterestRate=10


 IF(@LOANMTH>0)      
    BEGIN
         INSERT INTO tblFinalTable
         SELECT CPFNO,LOANMTH,(((EMP_OP-L_GRANT)*@InterestRate/100)/12)*((12-(LOANMTH))+1) AS EmpInterest FROM tblmyStoreTable 
		
    END
 ELSE
    BEGIN
         INSERT INTO tblFinalTable
		 SELECT CPFNO,LOANMTH,(EMP_OP*@InterestRate/100) as EmpInterest FROM tblmyStoreTable 
    END

推荐答案

如果我正确地得到标准，可以使用选择案例查询来获得所需的结果。

A select case query can be used to get the desired result if I get the criteria correctly.

--query like so or similar can be used for the insert into tblFinalTable
SELECT 
	*,
	Case 
		When LOANMTH > 0 Then
			(((EMP_OP-L_GRANT)*@InterestRate/100)/12)*((12-(LOANMTH))+1)
		Else
			(EMP_OP*@InterestRate/100)
		End as EmpInterest
FROM tblmyStoreTable 
;
--note: make sure the number of columns in the insert and the select match

不确定tblFinalTable表的目的是否可以从tblmyStoreTable表派生，如上面的select查询中所示。< br $> b $ b

了解CASE（Transact-SQL）： http://msdn.microsoft.com/en-GB/library/ms181765(v = sql.105）.aspx [ ^ ]

问题是 tblFinalTable 包含五列，但是 SELECT 语句只返回三列。



尝试：

The problem is that tblFinalTable contains five columns, but your SELECT statement only returns three columns.

Try:

INSERT INTO tblFinalTable
SELECT
    CPFNO,
    LOANMTH,
    EMP_OP,
    L_GRANT,
    CASE
        WHEN @LoanMth > 0 THEN (((EMP_OP - L_GRANT) * @InterestRate / 100) / 12) * ((12 - LOANMTH) + 1)
        ELSE (EMP_OP * @InterestRate / 100)
    END
FROM
    tblMyStoreTable
;

这篇关于如何在计算兴趣后将数据从一个表插入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！