Can't get this SQL to work?

I'm ok with coding SQL statements but I'm far from great. This code below has got me stumped. I think mainlyl with how to use Alias's in UPDATE/INSERT statements. The parameters I'm using will obviously be passed in thru a Stored Procedure. What am I missing here?

   
BEGIN
      DECLARE @mth      int
      DECLARE @yr            int
      
      SET @mth = 9
      SET @yr = 2013
      
      SELECT      C.iMonth,
                  C.iYear,
                  B.subAcctID AS Account,
                  A.budgetAmount AS Budget,
                  SUM(C.Amount) AS Actual
        INTO  tmpTable
        FROM  Allocations A INNER JOIN SubAccounts B ON A.subAcctID = B.subAcctID
                  LEFT JOIN Transactions C ON B.subAcctName = C.acctName
      WHERE C.iMonth = @mth
      GROUP BY B.subAcctID, C.iYear, C.iMonth, B.subAcctName,A.budgetAmount
    ORDER BY B.subAcctID, C.iMonth
   
    IF EXISTS (SELECT 1
                        FROM tmpTable A INNER JOIN AccountBalances B ON A.subAcctID = B.acctID
                     WHERE B.iMonth = @mth AND B.iYear = @yr)
            BEGIN                     
                  UPDATE AccountBalances
                     SET B.budgetAmount = A.budgetAmount,
                           B.actualAmount = A.Actual
                    FROM AccountBalances B
                   WHERE B.iMonth = @mth AND B.iYear = @yr AND A.acctID = B.Account
            END
      ELSE
            BEGIN
                  INSERT INTO AccountBalances
                        (acctID,iMonth,iYear,budgetAmount,actualAmount)
                        VALUES
                        (A.Account,@mth,@yr,A.Budget,A.Actual)
            END
END
BlakeMcKennaAsked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> I think mainlyl with how to use Alias's
Ten bucks says if you look at this code two months from now, it won't be intuitive what a, b, and c is.  Better to use aliases that give some idea of what the table is, such as s for sales, o for orders, c for customers, etc.

>Can't get this SQL to work?
Define 'not work'.  Does it never insert or update a row, throw an error, what?
0
Aneesh RetnakaranDatabase AdministratorCommented:
BEGIN
      DECLARE @mth      int
      DECLARE @yr            int
     
      SET @mth = 9
      SET @yr = 2013
     
      SELECT      C.iMonth,
                  C.iYear,
                  B.subAcctID AS Account,
                  A.budgetAmount AS Budget,
                  SUM(C.Amount) AS Actual
        INTO  #tmpTable
        FROM  Allocations A INNER JOIN SubAccounts B ON A.subAcctID = B.subAcctID
                  LEFT JOIN Transactions C ON B.subAcctName = C.acctName
      WHERE C.iMonth = @mth
      GROUP BY B.subAcctID, C.iYear, C.iMonth, B.subAcctName,A.budgetAmount
    ORDER BY B.subAcctID, C.iMonth
   

    UPDATE B
        SET B.budgetAmount = A.budgetAmount,
            B.actualAmount = A.Actual
      FROM #tmpTable A
      INNER JOIN AccountBalances B ON A.subAcctID = B.acctID
                     WHERE B.iMonth = @mth AND B.iYear = @yr         
    IF @@ROWCOUNT  = 0
      BEGIN
                  INSERT INTO AccountBalances
                        (acctID,iMonth,iYear,budgetAmount,actualAmount)
                  SELECT A.Account,iMonth,iYear,A.Budget,A.Actual FROM #tmpTable
     END
END
0
BlakeMcKennaAuthor Commented:
I'm getting errors while trying to execute it. See screenshot!
Screenshot.jpg
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

BlakeMcKennaAuthor Commented:
I figured it out by just playing around with it and process of elimination!
0
BlakeMcKennaAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for BlakeMcKenna's comment #a39511895

for the following reason:

I figured it out on my own.
0
Aneesh RetnakaranDatabase AdministratorCommented:
did you check the solution I provided ???
0

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
BlakeMcKennaAuthor Commented:
Actually, I didn't...my apologies. I will try try your code now...
0
BlakeMcKennaAuthor Commented:
OK,

Here are the errors I got running your code (see screenshot).
Screenshot.jpg
0
BlakeMcKennaAuthor Commented:
Ok,

I figured out your code...simple error. Your code DID work!

Thanks for your help!
0
BlakeMcKennaAuthor Commented:
Aneeshattingal code solution did work and worked well!
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.