Select Into Temporary Tables.

I am fininishing up a reporting application and am having a really rough time with the last part.  I need to create a stored procedure that adds totaled columns from another table.  I will then have to create columns that will be added to the report.  Can someone help me?


DECLARE @Totaltbl TABLE (
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))

INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) SELECT subTTLPer=( Table2.col1 + Table2.col2 + Table2.col3), subTTLYTD=(Table2.col4 + Table2.col5 + Table2.col6), subTTLJTD=(Table2.col7 + Table2.col8 + Table2.col9)

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = subTTLPer, YTDTTL = subTTLYTD, JTDTTL = subTTLJTD

Go

SELECT Table2.col_10, Table2.col_11, Table2.col_12

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12

Go

SELECT sumCurPerAdj = SUM(Table1.Col1), sumFYDamt = SUM(Table1.Col2), sumITDamt = SUM(Table1.Col3)

FROM dbo.Table1

UPDATE @RevTotalstbl SET PeriodTTL = sumCurPerAdj, YTDTTL = sumFYDamt, JTDTTL = sumITDamt

Open in new window

Annette Wilson, MSISSr. Programmer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bhavesh ShahLead AnalysistCommented:
Hi,

sorry but didnt understand exactly what you doing but i did some changes as per my understanding

Please check it.
DECLARE @Totaltbl TABLE 
(
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2)
)

INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) 
SELECT (col1 + col2 + col3), 
	   (col4 + col5 + col6), 
	   (col7 + col8 + col9)
FROM dbo.Table2


Go

SELECT Table2.col_10, Table2.col_11, Table2.col_12

FROM dbo.Table2

UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12

Go

INSERT INTO @RevTotalstbl 
(PeriodTTL, YTDTTL, JTDTTL) 

SELECT SUM(Table1.Col1), SUM(Table1.Col2), SUM(Table1.Col3)
FROM dbo.Table1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
I'm trying to do the following but I'm still getting the following error:
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@Ttotaltbl".
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@Ttotaltbl".

And When these three sets of data have been inserted into the temporary table, I need to get a total of the three columns and display them in a report.  The report will have three parameters.


Declare @Ttotaltbl TABLE (PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))INSERT INTO @Totaltbl (PeriodTTL, YTDTTL, JTDTTL) SELECT subTTLPer=( Table2.col1 + Table2.col2 + Table2.col3), subTTLYTD=(Table2.col4 + Table2.col5 + Table2.col6), subTTLJTD=(Table2.col7 + Table2.col8 + Table2.col9)
FROM dbo.Table2
UPDATE @Totaltbl SET PeriodTTL = subTTLPer, YTDTTL = subTTLYTD, JTDTTL = subTTLJTD
--Go
INSERT INTO @Ttotaltbl (PeriodTTL, YTDTTL, JTDTTL
SELECT Table2.col_10, Table2.col_11, Table2.col_12
FROM dbo.Table2
UPDATE @Totaltbl SET PeriodTTL = col_10, YTDTTL = col_11, JTDTTL = col_12
--Go
INSERT INTO @Ttotaltbl (PeriodTTL, YTDTTL, JTDTTL)
SELECT sumCurPerAdj = SUM(Table1.Col1), sumFYDamt = SUM(Table1.Col2), sumITDamt = SUM(Table1.Col3)
FROM dbo.Table1



Bhavesh ShahLead AnalysistCommented:
Hi,

"@Ttotaltbl" is not found in given code.
Please can you share your full code.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
@Ttotaltbl is in the code three times.  Maybe I'm not declaring the table variable correctly.

It's in the first line:
Declare @Ttotaltbl TABLE (PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))
Bhavesh ShahLead AnalysistCommented:
Hello Dear,

Good Day !!!
If you check first line in given code, its

DECLARE @Totaltbl TABLE (
PeriodTTL decimal (11,2), YTDTTL decimal (11,2), JTDTTL decimal (11,2))

Annette Wilson, MSISSr. Programmer AnalystAuthor Commented:
Marvelous... simply marvelous
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.