How to save stored procedure result in new table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i want save my stored procedure result in new table

this is my procedure

SQL

SELECT 
    PaymentTracker1.*
    , convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
    ,(Numbers.N * MonthlyPay) AS DueAmount
FROM 
    PaymentTracker1 
    INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N 
    
    where  datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and  datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
    PaymentTracker1.Name
    ,Numbers.N

how to save resulted value of this procedure into new table

Posted 1-Nov-15 20:43pm

Member 11382784

Updated 1-Nov-15 21:37pm

Naveen.Sanagasetti

v2

Add a Solution

3 solutions

Solution 2

create a temp table

SQL

CREATE TABLE #tmp
(
   COL1 INT,
   COL2 INT
)

write insert statement and execute the stored procedure

SQL

INSERT INTO #tmp
Exec SpName 'Params'

Posted 1-Nov-15 20:55pm

write2varun

Comments

Member 11382784 2-Nov-15 3:02am

thanks for your help sir ,
can we do edit update in grid (asp.net c#)with temp table and is tamp table delete automaticalyy

write2varun 2-Nov-15 3:11am

you will have to drop the table
drop table #tmp
you can add the temp table in the same sp and retrieve and bind the value into dataset or datatable and bind it with the grid and perform the update, retrieve the value from the grid and save it via table method or via dataset method

Solution 4

Try this

SQL

INSERT NEW_TABLE
EXEC SPNAME

Posted 1-Nov-15 21:44pm

Naveen.Sanagasetti

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)

PANKAJMAURYA · Accepted Answer · 2015-11-01T21:02:00

Solution 3

you can try SELECT INTO. This will create a new table for you, which can be temporary if you want by prefixing the table name with a pound sign (#).

SQL

SELECT *
INTO #tempTableName
FROM TableName

your query will be

SQL

SELECT PaymentTracker1.*, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
INTO #tempResult
FROM PaymentTracker1
INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N
where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

If you want to store it in permanent table then first you have to define a table with exactly same columns which are you selecting in query the use this table in above query.

Posted 1-Nov-15 21:02pm

PANKAJMAURYA

Comments

Member 11382784 2-Nov-15 3:20am

thanks for your help sir..
i want to save the result in permanent table,
and i have created a table with same columns , and how to define that in procedure .
i have tried but iam getting error There is already an object named 'MYPaymentTracker1' in the database.

SELECT PaymentTracker1.*, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
INTO [MYPaymentTracker1]
FROM PaymentTracker1
INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N
where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

[no name] 2-Nov-15 3:33am

What is your table name in which you want to store data ?

Member 11382784 2-Nov-15 3:42am

MYPaymentTracker1

[no name] 2-Nov-15 3:53am

You can try
INSERT INTO MYPaymentTracker1 SELECT PaymentTracker1.*, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
FROM PaymentTracker1
INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N
where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

Member 11382784 2-Nov-15 4:02am

am getting error ..Incorrect syntax near the keyword 'INTO'.

[no name] 2-Nov-15 4:27am

You can try
INSERT INTO MYPaymentTracker1(specify column names here) SELECT PaymentTracker1.*, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
FROM PaymentTracker1
INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N
where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

Member 11382784 3-Nov-15 0:43am

am getting error sir
Msg 264, Level 16, State 1, Line 1
The column name 'DueDate' is specified more than once in the SET clause. A column cannot be assigned more than one value in the same SET clause. Modify the SET clause to make sure that a column is updated only once. If the SET clause updates columns of a view, then the column name 'DueDate' may appear twice in the view definition.

INSERT INTO MYPaymentTracker1(ID,Name,InvestmentType,Amount,MonthlyPay,NumberOfMonths,StartDate,EndDate,Phone,Address,DueDate,DueDate) SELECT PaymentTracker1.*, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
FROM PaymentTracker1
INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N
where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N

[no name] 3-Nov-15 0:58am

you write DueDate two times in
MYPaymentTracker1(ID,Name,InvestmentType,Amount,MonthlyPay,NumberOfMonths,StartDate,EndDate,Phone,Address,DueDate,DueDate)

I think it should be
MYPaymentTracker1(ID,Name,InvestmentType,Amount,MonthlyPay,NumberOfMonths,StartDate,EndDate,Phone,Address,DueDate,DueAmount)

Member 11382784 3-Nov-15 1:10am

i got it sir , thank you

Member 11382784 3-Nov-15 1:14am

one small help sir ...
By using above query am trying to retrieve values of this month only , but the query is returning same month next year value also returning

please find the output image of my query

http://i.stack.imgur.com/LMg1m.png

i want to retrieve only current month value sir