Click here to Skip to main content
15,881,424 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
sir
i want to create temporary table and  insert data through store procedure and query 


SQL
<pre>Alter PROCEDURE  dbo.sp_ExpenMonthWise
(
@schemeno char(20),
@sch_amount nvarchar(max),
@Desciption nvarchar(2000),
@sdate smalldatetime,
@Exp_upto_Premonth nvarchar(max),
@Exp_Upto nvarchar(max),
@Exp_During nvarchar(max),
@ExpUpto nvarchar(max),
@PreMonth varchar(50),
@CurrMonth varchar(50)
)
AS
BEGIN
   IF OBJECT_ID('tempdb..#Temp') IS NOT NULL 
BEGIN 
    DROP TABLE #Temp 
END
   CREATE TABLE #Temp
  (
     TempID  INT IDENTITY(1,1) PRIMARY KEY, schemeno char(20), sch_amount nvarchar(max),Desciption nvarchar(2000),sdate smalldatetime,Exp_upto_Premonth nvarchar(max),

       Exp_Upto nvarchar(max),Exp_During nvarchar(max),ExpUpto nvarchar(max),PreMonth Varchar(50),CurrMonth Varchar(50)
  );
  
	INSERT INTO #Temp(schemeno,sch_amount,Desciption,sdate,Exp_upto_Premonth,Exp_Upto,Exp_During,ExpUpto,PreMonth,CurrMonth) VALUES 
	(@schemeno,@sch_amount,@Desciption,@sdate,@Exp_upto_Premonth,@Exp_Upto,@Exp_During,@ExpUpto,@PreMonth,@CurrMonth)
	 DECLARE @totalRecords INT
     DECLARE @I INT

    SELECT @I = 1
    SELECT @totalRecords = COUNT(TempID) FROM #Temp
    WHILE (@I <= @totalRecords)
     BEGIN
    SELECT * FROM #Temp WHERE TempID = @I
     SELECT @I = @I + 1
     END


 SELECT schemeno, sch_amount, Desciption, sdate, Exp_upto_Premonth,Exp_Upto,Exp_During,ExpUpto,PreMonth,CurrMonth
  FROM #Temp 
    
END;



Insert code through loop:-

C#
foreach (DataTable table in Insrtds.Tables)
            {

                foreach (DataRow row in table.Rows)
                {
                    //string monyr = row["monyr"].ToString();
                    //var monyrarr = monyr.ToCharArray();
                    //string finyrr = (monyrarr[4].ToString() + monyrarr[5].ToString());

                    SqlCommand cmd = new SqlCommand("dbo.sp_ExpenMonthWise", con);
                    cmd.CommandType = CommandType.StoredProcedure;  
                    cmd.Parameters.AddWithValue("@schemeno", row["schemeno"]);
                    cmd.Parameters.AddWithValue("@sch_amount", row["sch_amount"]);
                    cmd.Parameters.AddWithValue("@Desciption", row["disp"]);
                    cmd.Parameters.AddWithValue("@sdate", row["schdate"]);
                    cmd.Parameters.AddWithValue("@Exp_upto_Premonth", row["Premonth"]);
                    cmd.Parameters.AddWithValue("@Exp_upto", row["ExpUpto"]);
                    cmd.Parameters.AddWithValue("@Exp_During", row["CurrMonExp"]);
                    cmd.Parameters.AddWithValue("@ExpUpto", row["ExpUptoNow"]);
                    cmd.Parameters.AddWithValue("@PreMonth", PreMonthName);
                    cmd.Parameters.AddWithValue("@CurrMonth", CurrMonthName);
                    cmd.ExecuteNonQuery();


What I have tried:

i want to create temporary table and  insert data  
Posted
Updated 22-May-18 21:39pm
v2

A sample query

SELECT * INTO #tempEntity  FROM  (SELECT ENTITY_ID,ENTITY_NAME FROM EntityTable)
WHERE ENTITY_ID <= 3
 
Share this answer
 
v2
Comments
Nishant.Chauhan80 23-May-18 3:42am    
please check my code
Praveen_P 23-May-18 3:47am    
What's the error you are getting ?
Nishant.Chauhan80 23-May-18 3:48am    
multiple records not insert temporary table through looping
Santosh kumar Pithani 23-May-18 4:19am    
why you are inserting records in #temp table and using while?
Nishant.Chauhan80 23-May-18 4:24am    
because i want generate runtime report on new window when close report then drop table
If you are waiting for permission, then consider it given: you may go right ahead and do it.

If that's not what you need, then start with Google: create temporary table and insert data through store procedure - Google Search[^] and try to refine your problem down to a rather less vague description. We can't answer a question you don't ask, and you haven't asked us any question here!
 
Share this answer
 
Comments
Nishant.Chauhan80 23-May-18 3:42am    
please see my query and code
OriginalGriff 23-May-18 3:54am    
And?
What happens? Any errors? How did you check? When did you check?
We can't run your code - we don't have access to your data or your database - so you need to give us as much relevant information as you can.
OriginalGriff 23-May-18 3:56am    
Actually, no - look at your SP.

You delete the temp table each time you execute the SP!
So how would you expect data to survive when each time you go round your loop you delete it?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900