data to be inserted in a table

Question

0.00/5 (No votes)

See more:

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 ,

please give me an idea

Posted 4-Aug-12 1:22am

sk. maqdoom ali

Add a Solution

Comments

Santhosh Kumar Jayaraman 4-Aug-12 7:25am

what is meant by approval? If some one has to give approval explicitly?

sk. maqdoom ali 4-Aug-12 7:26am

yes, from admin level approval is there

Santhosh Kumar Jayaraman 4-Aug-12 7:28am

Then i dont think you can go with temptable concepts. You can copy into someother table and then after approval, copy to main table.. Once records are copied into maintable, delete records from other table.

sk. maqdoom ali 4-Aug-12 8:19am

alter 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

i am using the above procedure but after execution i am getting the result as Invalid object name '#Closure_Copy'.

Santhosh Kumar Jayaraman 4-Aug-12 8:26am

The temp table will hold only till the current connection for the user is active. Thats what i said, save it in some other physical table.
Create new table Account_closure1 and save it there. Then finally once it gets approval, remove from account_closure1 and insert into account_Closure

sk. maqdoom ali 4-Aug-12 10:15am

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

please tell me solution

Santhosh Kumar Jayaraman 4-Aug-12 10:17am

I will suggest you to have another physical table. If you go with temp table concept it wont hold good.

Santhosh Kumar Jayaraman 4-Aug-12 10:25am

Also wats the reason to save there in temp table. Once the session is closed or conenction to the user is terminated, then temp table will be gone? Then y do u save in temptable? I think you have downvoted my solution which i suggested to have another physical table. Then please come up with your own solution

sk. maqdoom ali 4-Aug-12 10:46am

sorry sir i am not downvoted you i am trying do using this method

Santhosh Kumar Jayaraman 4-Aug-12 10:20am

If you stil want to save in temp table, then how long the data will be there? Suppose if you insert few records inside temp table with no approval. Now you have closed connection and when you connect back, are you able to see your temp table?

sk. maqdoom ali 4-Aug-12 10:44am

yes u r correct but my requirement is before closing the connection the data must be there ..means for one connection i will create one temptable only after closing the connection its not required , multiple members will enter the data but the inserted data must be entered into that existed table for that connection

Santhosh Kumar Jayaraman 4-Aug-12 11:28am

but temptable is only for that session.In general why do you want to save ion temp table? you cannot query that when you come back next time..

Santhosh Kumar Jayaraman 4-Aug-12 8:30am

Thats what i aleady told in solution1

sk. maqdoom ali 6-Aug-12 5:26am

sir, i updated it like this

ALTER PROCEDURE [dbo].[sp_CSP_SaveAccount_Closure_Temp_Table]

@Account_Id INT=NULL,
@Application_Ref_No NVARCHAR(20),
@Balance_Repayable FLOAT=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,
@CheckBook_Status TINYINT=NULL,
@ATMCard_Status TINYINT=NULL,
@Reason_Closure NVARCHAR(150),
@User_ID INT=NULL,
@IP_Address NVARCHAR(15)

AS
BEGIN

IF OBJECT_ID('tempdb..##Closure_Copy') IS NULL
BEGIN
CREATE TABLE ##Closure_Copy(

[Account_Id] [int] NULL,
[Application_Ref_No] [nvarchar](20) NULL,
[Balance_Repayable] [float] 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,
[CheckBook_Status] [tinyint] NULL,
[ATMCard_Status] [tinyint] NULL,

[Reason_Closure] [text] NULL,
[User_ID] [int] NULL,
[IP_Address] [nvarchar](15) NULL,
[Date_Time] [datetime] NULL,
)

INSERT INTO ##Closure_Copy(
Account_Id,
Application_Ref_No,
Balance_Repayable ,
Closing_Type ,
Closing_Interest_Rate,
Source_Funds ,
Reason_Balance_Transfer ,
ROI_OfferedBy_Bank ,
Reason_Balance_Transfer_Others ,
CheckBook_Status,
ATMCard_Status,
Reason_Closure,
[User_ID],
IP_Address,
Date_Time

)
values(@Account_Id,
@Application_Ref_No,
@Balance_Repayable,
@Closing_Type ,
@Closing_Interest_Rate,
@Source_Funds ,
@Reason_Balance_Transfer ,
@ROI_OfferedBy_Bank ,
@Reason_Balance_Transfer_Others ,
@CheckBook_Status,
@ATMCard_Status,
@Reason_Closure,
@User_ID,
@IP_Address,
GETDATE()
)

END

ELSE
BEGIN
INSERT INTO ##Closure_Copy(
Account_Id,
Application_Ref_No,
Balance_Repayable,
Closing_Type ,
Closing_Interest_Rate,
Source_Funds ,
Reason_Balance_Transfer ,
ROI_OfferedBy_Bank ,
Reason_Balance_Transfer_Others ,
CheckBook_Status,
ATMCard_Status,
Reason_Closure,

[User_ID],
IP_Address,
Date_Time)
values(@Account_Id,
@Application_Ref_No,
@Balance_Repayable,
@Closing_Type ,
@Closing_Interest_Rate,
@Source_Funds ,
@Reason_Balance_Transfer ,
@ROI_OfferedBy_Bank ,
@Reason_Balance_Transfer_Others ,
@CheckBook_Status,
@ATMCard_Status,
@Reason_Closure,
@User_ID,
@IP_Address,
GETDATE()
)
END
END

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-04T01:28:00

Solution 1

Then i dont think you can go with temptable concepts. You can copy into someother table and then after approval, copy to main table.. Once records are copied into maintable, delete records from other table.

Posted 4-Aug-12 1:28am

Santhosh Kumar Jayaraman