SQL Server - i want to insert data at first into the temptable then after the approval=true then only

hi,,


 
i want to insert data at first into the temptable then after the approval=true then only that data must be inserted into the corresponding main table ,,,,,

CREATE PROCEDURE sp_CSP_SaveTemp_Table
    @ID  INT,
    @Account_Id INT=NULL,
    @Application_Ref_No NVARCHAR(20),
    @Balance_Repayable FLOAT=NULL,
    @CheckBook_Status TINYINT=NULL,
    @ATMCard_Status TINYINT=NULL,
    @Closing_Type TINYINT=NULL,
    @Closing_Interest_Rate TINYINT=NULL,
    @Source_Funds TINYINT=NULL,
    @Reason_Balance_Transfer TINYINT=NULL,
    @ROI_OfferedBy_Bank FLOAT=NULL,
    @Reason_Balance_Transfer_Others NVARCHAR(150),
    @Mode_Payment TINYINT=NULL,
    @Reason_Closure NVARCHAR(150),
    @Is_Manager_Approved tinyint =NULL,
    @Reason_Rejeted TEXT=NULL,
    @User_ID INT=NULL,
    @IP_Address NVARCHAR(15),
    @ApprovedBy INT=NULL,
    
    @Insertion_Approved BIT=NULL


AS
BEGIN

IF @Insertion_Approved IS NULL

BEGIN

CREATE TABLE ##Closure_Copy(
    [ID] [int] ,
    [Account_Id] [int] NULL,
    [Application_Ref_No] [nvarchar](20) NULL,
    [Balance_Repayable] [float] NULL,
    [CheckBook_Status] [tinyint] NULL,
    [ATMCard_Status] [tinyint] NULL,
    [Closing_Type] [tinyint] NULL,
    [Closing_Interest_Rate] [float] NULL,
    [Source_Funds] [tinyint] NULL,
    [Reason_Balance_Transfer] [tinyint] NULL,
    [ROI_OfferedBy_Bank] [float] NULL,
    [Reason_Balance_Transfer_Others] [nvarchar](150) NULL,
    [Mode_Payment] [tinyint] NULL,
    [Reason_Closure] [text] NULL,
    [Is_Manager_Approved] [tinyint] NULL,
    [Reason_Rejeted] [text] NULL,
    [User_ID] [int] NULL,
    [IP_Address] [nvarchar](15) NULL,
    [Date_Time] [datetime] NULL,
    [ApprovedBy] [int] NULL)
    
    
    INSERT INTO ##Closure_Copy(ID ,
    Account_Id,
    Application_Ref_No,
    Balance_Repayable,
    CheckBook_Status,
    ATMCard_Status,
    Closing_Type,
    Closing_Interest_Rate,
    Source_Funds,
    Reason_Balance_Transfer,
    ROI_OfferedBy_Bank,
    Reason_Balance_Transfer_Others,
    Mode_Payment,
    Reason_Closure,
    Is_Manager_Approved,
    Reason_Rejeted,
    [User_ID],
    IP_Address,
    Date_Time,
    ApprovedBy)
values(@id,@Account_Id,
    @Application_Ref_No,
    @Balance_Repayable,
    @CheckBook_Status,
    @ATMCard_Status,
    @Closing_Type,
    @Closing_Interest_Rate,
    @Source_Funds,
    @Reason_Balance_Transfer,
    @ROI_OfferedBy_Bank,
    @Reason_Balance_Transfer_Others,
    @Mode_Payment,
    @Reason_Closure,
    @Is_Manager_Approved,
    @Reason_Rejeted,
    @User_ID,
    @IP_Address,
    GETDATE(),
    @ApprovedBy)

END


ELSE

BEGIN

INSERT INTO Account_Closure(
    Account_Id,
    Application_Ref_No,
    Balance_Repayable,
    CheckBook_Status,
    ATMCard_Status,
    Closing_Type,
    Closing_Interest_Rate,
    Source_Funds,
    Reason_Balance_Transfer,
    ROI_OfferedBy_Bank,
    Reason_Balance_Transfer_Others,
    Mode_Payment,
    Reason_Closure,
    Is_Manager_Approved,
    Reason_Rejeted,
    [User_ID],
    IP_Address,
    Date_Time,
    ApprovedBy)
    
    VALUES(@Account_Id,
    @Application_Ref_No,
    @Balance_Repayable,
    @CheckBook_Status,
    @ATMCard_Status,
    @Closing_Type,
    @Closing_Interest_Rate,
    @Source_Funds,
    @Reason_Balance_Transfer,
    @ROI_OfferedBy_Bank,
    @Reason_Balance_Transfer_Others,
    @Mode_Payment,
    @Reason_Closure,
    @Is_Manager_Approved,
    @Reason_Rejeted,
    @User_ID,
    @IP_Address,
    GETDATE(),
    @ApprovedBy)   
END
END

   from the above procedure i have a problem that for every new insertion new temporary table is created . i don't want this for every new insertion that newly inserted data must be inserted after the previous data which is inserted before by the member,,,,,,,,,,,, the temporary table must be there for every insertion

What I think you're asking is that you want the temp table to go away after each execution of your stored procedure.

Put this at the end of the stored proc:

The way you are creating table is exist till the execution of code. Once you execution finish it will be destory from the sql server. What you need to do is , you need to create a table in SQL itself which is not temporary. Everytime you insert the data will be store in that table.

Now once you will update that data (approval=true). I guess you have this field in you database. So when you update the value of "approval" field, just fire a update trigger or do some insertion code on that table and store that particular entry in your permenant table which you will use further and delete that particular entry from that temporary table. So in that way size of that table will not be exceed..